{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "e7bf671c",
   "metadata": {},
   "source": [
    "## 临时修改notebook宽度，方便截图适配word宽度\n",
    "\n",
    "notebook默认宽度太宽。截图最好包含整个灰色的边框，并且截图中的代码要比word正文的字体要略小一点，这样比较合理美观。根据你的电脑分辨率，调整width参数，比如在我的电脑，43%是比较合理的设置。\n",
    "\n",
    "这种修改是临时修改，只在本次有效。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "id": "81601deb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style>.container { width:60% !important; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "from IPython.display import display, HTML\n",
    "\n",
    "display(HTML(\"<style>.container { width:60% !important; }</style>\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d0e1596c",
   "metadata": {},
   "source": [
    "# Pandas数据结构\n",
    "Pandas是处理表格数据最常用的库，使用范围涵盖数据分析整个流程。Pandas包含两种数据结构(数据类型):\n",
    "1. Series\n",
    "2. DataFrame\n",
    "\n",
    "pandas可以\"整体\"地操作你的数据，比如对某列的每个元素应用一个函数，而不需要通过\"循环\"依次处理，这是它的强大方便之处。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "21940546",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 导入库\n",
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b430bcc2",
   "metadata": {},
   "source": [
    "Series是带有标签(index)的**一维数组**，Series数据类型的结构分为索引(index)和数值(values)。\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "e642820b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a    1\n",
       "b    3\n",
       "c    5\n",
       "d    7\n",
       "e    9\n",
       "dtype: int64"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = pd.Series([1, 3, 5, 7, 9], index=list('abcde'))\n",
    "s"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "5d70011c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['a', 'b', 'c', 'd', 'e'], dtype='object')"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# s的标签：index\n",
    "s.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "ea828c10",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([1, 3, 5, 7, 9], dtype=int64)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# s的数据：values\n",
    "s.values"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7726e5bd",
   "metadata": {},
   "source": [
    "DataFrame为带有行标签(index)和列标签(columns)的**二维数组**，DataFrame的使用频率非常高。可以从两个角度理解DataFrame:\n",
    "\n",
    "● DataFrame可以看作是Numpy二维数组的扩展版。Numpy二维数组的行和列的索引只能用0, 1, 2...数字表示。DataFrame的行和列可以采用更有意义的标签，数据更直观和易理解。\n",
    "\n",
    "● DataFrame还可以看作是多个Series按水平方向\"拼\"起来的结构，每个Series为一列，列与列之间共享相同的行标签，每一列的数据类型可以不同。DataFrame可以包含很多列的Series，相比只有一列的Series，DataFrame的数据的表达力更强大。\n",
    "\n",
    "DataFrame数据类型的结构可以分为行索引(index)，列索引(columns)以及数值(values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "f5615210",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>x</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y</th>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>z</th>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   a   b   c   d\n",
       "x  1   2   3   4\n",
       "y  5   6   7   8\n",
       "z  9  10  11  12"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame([[1, 2, 3, 4],\n",
    "                   [5, 6, 7, 8],\n",
    "                   [9, 10, 11, 12]], index=list('xyz'), columns=list('abcd'))\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "c9de9fcf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['x', 'y', 'z'], dtype='object')"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# df的行标签(index)\n",
    "df.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "1aad7ce3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['a', 'b', 'c', 'd'], dtype='object')"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# df的列标签(columns)\n",
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "f4d30446",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[ 1,  2,  3,  4],\n",
       "       [ 5,  6,  7,  8],\n",
       "       [ 9, 10, 11, 12]], dtype=int64)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# df的数值(values): numpy二维数组\n",
    "df.values"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d6d83b50",
   "metadata": {},
   "source": [
    "# 数据的导入导出\n",
    "pandas可以导入各种来源和格式的数据，如：\n",
    "\n",
    "导入csv: df = pd.read_csv()      \n",
    "\n",
    "导入excel: df = pd.read_excel()   \n",
    "\n",
    "导出csv: df.to_csv('new.csv')    \n",
    "\n",
    "导出excel: df.to_excel('new.xlsx')  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "512ba1d1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2022-02-10</th>\n",
       "      <td>3481.91</td>\n",
       "      <td>3488.86</td>\n",
       "      <td>3464.22</td>\n",
       "      <td>3485.91</td>\n",
       "      <td>3.556663e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-02-11</th>\n",
       "      <td>3472.28</td>\n",
       "      <td>3500.15</td>\n",
       "      <td>3459.33</td>\n",
       "      <td>3462.95</td>\n",
       "      <td>3.613561e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-02-14</th>\n",
       "      <td>3451.85</td>\n",
       "      <td>3457.26</td>\n",
       "      <td>3415.45</td>\n",
       "      <td>3428.88</td>\n",
       "      <td>3.152744e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-02-15</th>\n",
       "      <td>3428.04</td>\n",
       "      <td>3447.49</td>\n",
       "      <td>3421.64</td>\n",
       "      <td>3446.09</td>\n",
       "      <td>2.755593e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-02-16</th>\n",
       "      <td>3457.07</td>\n",
       "      <td>3475.06</td>\n",
       "      <td>3453.80</td>\n",
       "      <td>3465.83</td>\n",
       "      <td>2.749939e+10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               open     high      low    close        volume\n",
       "2022-02-10  3481.91  3488.86  3464.22  3485.91  3.556663e+10\n",
       "2022-02-11  3472.28  3500.15  3459.33  3462.95  3.613561e+10\n",
       "2022-02-14  3451.85  3457.26  3415.45  3428.88  3.152744e+10\n",
       "2022-02-15  3428.04  3447.49  3421.64  3446.09  2.755593e+10\n",
       "2022-02-16  3457.07  3475.06  3453.80  3465.83  2.749939e+10"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 导入daily_price.csv\n",
    "df = pd.read_csv('daily_price.csv', index_col=0)# \n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "23ee8019",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 如果导出数据到一个excel的多个工作表，可以先构建一个ExcelWriter对象，然后分别写入，最后关闭ExcelWriter。\n",
    "# 例如，将df的前5行写入output.xlsx的'a'工作表，后10行写入output.xlsx的'b'工作表。\n",
    "writer = pd.ExcelWriter('output.xlsx')\n",
    "df.head(5).to_excel(writer, sheet_name='a')\n",
    "df.tail(10).to_excel(writer, sheet_name='b')\n",
    "writer.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "de05b912",
   "metadata": {},
   "source": [
    "# 数据描述: 熟悉你的数据\n",
    "查看df的**大小**：df.shape\n",
    "\n",
    "查看df的**数据类型**：df.dtypes\n",
    "\n",
    "查看df的**摘要**(行数、列数、数据类型、非空值数量等等): df.info()\n",
    "\n",
    "查看数据的**描述性统计信息**：df.describe()\n",
    "\n",
    "查看前几行：df.head(n=5)\n",
    "\n",
    "查看最后几行: df.tail(n=5)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "afad65e5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>x</th>\n",
       "      <td>1.0</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y</th>\n",
       "      <td>NaN</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>z</th>\n",
       "      <td>9.0</td>\n",
       "      <td>10</td>\n",
       "      <td>11.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     a   b     c    d\n",
       "x  1.0   2   NaN  4.0\n",
       "y  NaN   6   NaN  8.0\n",
       "z  9.0  10  11.0  NaN"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame([[1, 2, np.nan, 4],\n",
    "                   [np.nan, 6, np.nan, 8],\n",
    "                   [9, 10, 11, np.nan]], index=list('xyz'), columns=list('abcd'))\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "b6f17de0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3, 4)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 查看df的大小，几行几列构成\n",
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "c61840eb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a    float64\n",
       "b      int64\n",
       "c    float64\n",
       "d    float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 查看各列的数据类型\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "33c86e8b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 3 entries, x to z\n",
      "Data columns (total 4 columns):\n",
      " #   Column  Non-Null Count  Dtype  \n",
      "---  ------  --------------  -----  \n",
      " 0   a       2 non-null      float64\n",
      " 1   b       3 non-null      int64  \n",
      " 2   c       1 non-null      float64\n",
      " 3   d       2 non-null      float64\n",
      "dtypes: float64(3), int64(1)\n",
      "memory usage: 120.0+ bytes\n"
     ]
    }
   ],
   "source": [
    "# 查看df的摘要(行数、列数、数据类型、非空值数量等等)\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "2625790b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>2.000000</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>5.000000</td>\n",
       "      <td>6.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>6.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>5.656854</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.828427</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>2.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>4.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>3.000000</td>\n",
       "      <td>4.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>5.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>5.000000</td>\n",
       "      <td>6.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>6.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>7.000000</td>\n",
       "      <td>8.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>7.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>9.000000</td>\n",
       "      <td>10.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>8.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              a     b     c         d\n",
       "count  2.000000   3.0   1.0  2.000000\n",
       "mean   5.000000   6.0  11.0  6.000000\n",
       "std    5.656854   4.0   NaN  2.828427\n",
       "min    1.000000   2.0  11.0  4.000000\n",
       "25%    3.000000   4.0  11.0  5.000000\n",
       "50%    5.000000   6.0  11.0  6.000000\n",
       "75%    7.000000   8.0  11.0  7.000000\n",
       "max    9.000000  10.0  11.0  8.000000"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 查看数据的描述性统计信息\n",
    "df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "e6e55ff9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>x</th>\n",
       "      <td>1.0</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y</th>\n",
       "      <td>NaN</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     a  b   c    d\n",
       "x  1.0  2 NaN  4.0\n",
       "y  NaN  6 NaN  8.0"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 查看前2行\n",
    "df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "a00a5b13",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>y</th>\n",
       "      <td>NaN</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>z</th>\n",
       "      <td>9.0</td>\n",
       "      <td>10</td>\n",
       "      <td>11.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     a   b     c    d\n",
       "y  NaN   6   NaN  8.0\n",
       "z  9.0  10  11.0  NaN"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 查看后2行\n",
    "df.tail(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dc3ed613",
   "metadata": {},
   "source": [
    "# 数据清洗(一)：缺失值处理\n",
    "查看和统计缺失值：df.isnull(), df.notnull()\n",
    "\n",
    "删除缺失值：df.dropna(axis, how)\n",
    "\n",
    "填充缺失值；df.fillna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "4cea7f3e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>x</th>\n",
       "      <td>1.0</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y</th>\n",
       "      <td>NaN</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>z</th>\n",
       "      <td>9.0</td>\n",
       "      <td>10</td>\n",
       "      <td>11.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     a   b     c    d\n",
       "x  1.0   2   NaN  4.0\n",
       "y  NaN   6   NaN  8.0\n",
       "z  9.0  10  11.0  NaN"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame([[1, 2, np.nan, 4],\n",
    "                   [np.nan, 6, np.nan, 8],\n",
    "                   [9, 10, 11, np.nan]], index=list('xyz'), columns=list('abcd'))\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "d4d32a67",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>x</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y</th>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>z</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       a      b      c      d\n",
       "x  False  False   True  False\n",
       "y   True  False   True  False\n",
       "z  False  False  False   True"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 查看每个位置是否缺失: isnull()，缺失值为True\n",
    "df.isnull()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "18b7d2ae",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>x</th>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y</th>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>z</th>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       a     b      c      d\n",
       "x   True  True  False   True\n",
       "y  False  True  False   True\n",
       "z   True  True   True  False"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 查看每个位置是否缺失: notnull()，非缺失值为True\n",
    "df.notnull()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "a5b8c7c6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a    1\n",
       "b    0\n",
       "c    2\n",
       "d    1\n",
       "dtype: int64"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 每列的缺失值数量, 缺失值=True=1, 加起来即可\n",
    "df.isnull().sum(axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "ff7d88d4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "x    1\n",
       "y    2\n",
       "z    1\n",
       "dtype: int64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 每行的缺失值数量\n",
    "df.isnull().sum(axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4186d08e",
   "metadata": {},
   "source": [
    "df.dropna()主要有两个参数：\n",
    "\n",
    "axis=0表示按行删除，axis=1表示按列删除\n",
    "\n",
    "how='all', 全部na才删除；how='any'只要有一个na就删除，默认any"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "78a97915",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>x</th>\n",
       "      <td>1.0</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y</th>\n",
       "      <td>NaN</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>z</th>\n",
       "      <td>9.0</td>\n",
       "      <td>10</td>\n",
       "      <td>11.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     a   b     c    d\n",
       "x  1.0   2   NaN  4.0\n",
       "y  NaN   6   NaN  8.0\n",
       "z  9.0  10  11.0  NaN"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "aca6effd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [a, b, c, d]\n",
       "Index: []"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 按行删除，每一行都有na，全删掉了\n",
    "df.dropna(axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "6c01a089",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>b</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>x</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>z</th>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    b\n",
       "x   2\n",
       "y   6\n",
       "z  10"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 按列删除，acd列有na删掉了\n",
    "df.dropna(axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "c71ae92a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>x</th>\n",
       "      <td>1.0</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y</th>\n",
       "      <td>NaN</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>z</th>\n",
       "      <td>9.0</td>\n",
       "      <td>10</td>\n",
       "      <td>11.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     a   b     c    d\n",
       "x  1.0   2   NaN  4.0\n",
       "y  NaN   6   NaN  8.0\n",
       "z  9.0  10  11.0  NaN"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 按列删除，how='all', 全部na才删除\n",
    "df.dropna(axis=1, how='all')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "4971524e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>x</th>\n",
       "      <td>1.0</td>\n",
       "      <td>2</td>\n",
       "      <td>11.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y</th>\n",
       "      <td>5.0</td>\n",
       "      <td>6</td>\n",
       "      <td>11.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>z</th>\n",
       "      <td>9.0</td>\n",
       "      <td>10</td>\n",
       "      <td>11.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     a   b     c    d\n",
       "x  1.0   2  11.0  4.0\n",
       "y  5.0   6  11.0  8.0\n",
       "z  9.0  10  11.0  6.0"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 以每列平均值填充na\n",
    "df.fillna(df.mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "1c76abe8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>x</th>\n",
       "      <td>1.0</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y</th>\n",
       "      <td>1.0</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>z</th>\n",
       "      <td>9.0</td>\n",
       "      <td>10</td>\n",
       "      <td>11.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     a   b     c    d\n",
       "x  1.0   2   NaN  4.0\n",
       "y  1.0   6   NaN  8.0\n",
       "z  9.0  10  11.0  8.0"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 以:前一个有效值填充na\n",
    "df.fillna(method='ffill')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "e370b8db",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>x</th>\n",
       "      <td>1.0</td>\n",
       "      <td>2</td>\n",
       "      <td>11.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y</th>\n",
       "      <td>9.0</td>\n",
       "      <td>6</td>\n",
       "      <td>11.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>z</th>\n",
       "      <td>9.0</td>\n",
       "      <td>10</td>\n",
       "      <td>11.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     a   b     c    d\n",
       "x  1.0   2  11.0  4.0\n",
       "y  9.0   6  11.0  8.0\n",
       "z  9.0  10  11.0  NaN"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 以:后一个有效值填充na\n",
    "df.fillna(method='bfill')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "43554237",
   "metadata": {},
   "source": [
    "# 数据清洗（二）：重复值处理\n",
    "查看重复行：df.duplicated():  查看是否有重复的行，默认每一列都相同才被认为是重复行， 重复行默认只保留第一个\n",
    "\n",
    "删除重复行：df.drop_duplicates()：将重复行删除"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "50bca880",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>x</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>z</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>w</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   a  b  c  d\n",
       "x  1  2  3  4\n",
       "y  1  4  2  3\n",
       "z  1  2  3  4\n",
       "w  1  2  8  5"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame([[1, 2, 3, 4],\n",
    "                   [1, 4, 2, 3],\n",
    "                   [1, 2, 3, 4],\n",
    "                   [1, 2, 8, 5]], index=list('xyzw'), columns=list('abcd'))\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "0eb955c4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "x    False\n",
       "y    False\n",
       "z     True\n",
       "w    False\n",
       "dtype: bool"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# df.duplicated(): 查看是否有重复的行，默认每一列都相同才被认为是重复行， 重复行默认只保留第一个\n",
    "df.duplicated()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "a8a89649",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "x    False\n",
       "y    False\n",
       "z     True\n",
       "w     True\n",
       "dtype: bool"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# df.duplicated(subset): 也可以指定部分列重复即为重复行\n",
    "df.duplicated(subset=['a', 'b'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "d7200ae7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>x</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>w</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   a  b  c  d\n",
       "x  1  2  3  4\n",
       "y  1  4  2  3\n",
       "w  1  2  8  5"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# df.drop_duplicates(): 将重复行删除。\n",
    "df.drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "2e5232a0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>x</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   a  b  c  d\n",
       "x  1  2  3  4\n",
       "y  1  4  2  3"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# df.drop_duplicates(subset): 也可以指定部分列重复即为重复行，将其删除\n",
    "df.drop_duplicates(subset=['a', 'b'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cf5b2a84",
   "metadata": {},
   "source": [
    "# 数据清洗（三）：异常处理\n",
    "做一个箱体图或者小提琴图看一下数据的分布，或者用统计方法挑选出异常值。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "13b8ea62",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>x</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>z</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>w</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   a  b  c  d\n",
       "x  1  2  3  4\n",
       "y  1  4  2  3\n",
       "z  1  2  3  4\n",
       "w  1  2  8  5"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame([[1, 2, 3, 4],\n",
    "                   [1, 4, 2, 3],\n",
    "                   [1, 2, 3, 4],\n",
    "                   [1, 2, 8, 5]], index=list('xyzw'), columns=list('abcd'))\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "4bb6a8c1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<AxesSubplot:>"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAWoAAAD4CAYAAADFAawfAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjUuMSwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy/YYfK9AAAACXBIWXMAAAsTAAALEwEAmpwYAAAPDUlEQVR4nO3df2zc913H8dd7caYmcdaytZxgjPgPUAg2ZMMHWimgu6ZUg3aTQAjtpPHTkv9AcgtioplOqFTopE5CE1P4ISI8rWLjvClrJ5SKqAPfrRTRonPWVM1u+4Ot2boCW7XR7NqIJdabP+z0Rzj7vo4/X3/f3/PzIVl1el9//Oo77sunz32/9zV3FwAgrjcVHQAAsDmKGgCCo6gBIDiKGgCCo6gBILiJPBa9+eabfWpqKo+lk3n55Zd14MCBomOMDeaZFvNMqwzzXFlZedHdbxn2WC5FPTU1pV6vl8fSyXS7XdVqtaJjjA3mmRbzTKsM8zSzCxs9xtYHAARHUQNAcBQ1AARHUQNAcBQ1AASXqajN7A/M7LyZPWtmbTO7Ie9gwG7Ubrc1MzOjY8eOaWZmRu12u+hICGDk6Xlm9nZJ90j6cXe/ZGaflvR+SR/PORuwq7TbbTWbTS0uLmp1dVV79uzR3NycJKnRaBScDkXKuvUxIWmfmU1I2i/phfwiAbtTq9XS4uKi6vW6JiYmVK/Xtbi4qFarVXQ0FGzkM2p3/4aZ/Zmkr0m6JOkxd3/s2uPMbF7SvCRVKhV1u93EUdMaDAbhM5YJ89y+fr+v1dVVdbvdV+e5urqqfr/PbLep9D+f7r7ph6Tvk7Qs6RZJeyV9VtIHNvua2dlZj67T6RQdYawwz+2bnp725eVld39tnsvLyz49PV1gqvFQhp9PST3foFOzbH3cIemr7v4td78s6WFJP5vLbw1gF2s2m5qbm1On09GVK1fU6XQ0NzenZrNZdDQULMt7fXxN0rvNbL/Wtj6OSYr9Rh5ACV19wXBhYUH9fl9HjhxRq9XihURk2qN+ysxOSTor6YqkL0g6mXcwYDdqNBpqNBqleBMh7JxM757n7vdLuj/nLACAIbgyEQCCo6gBIDiKGgCCo6gBIDiKGgCCo6gBIDiKGgCCo6gBIDiKGgCCo6gBIDiKGgCCo6gBIDiKGgCCo6gBIDiKGgCCo6gBIDiKGgCCo6gBIDiKGgCCG1nUZnbYzJ5+3cdFM/v9HcgGAFC2u5B/WdI7JcnM9kj6hqRH8o0FALhqq1sfxyT9h7tfyCMMAOD/M3fPfrDZxySddfe/GPLYvKR5SapUKrNLS0vJQuZhMBhocnKy6Bhjg3mmxTzTKsM86/X6irtXhz2WuajN7M2SXpA07e7/vdmx1WrVe73eloPupG63q1qtVnSMscE802KeaZVhnma2YVFvZevjl7T2bHrTkgYApLWVom5IaucVBAAwXKaiNrP9kn5R0sP5xgEAXGvk6XmS5O6vSHpbzlkAAENwZSIABEdRA0BwFDUABEdRA0BwFDUABEdRA0BwFDUABEdRA0BwFDUABEdRA0BwFDUABEdRA0BwFDUABEdRA0BwFDUABEdRA0BwFDUABEdRA0BwFDUABEdRA0BwWe9CfpOZnTKzL5lZ38xuzTsYAGBNpruQS/qopDPu/mtm9mZJ+3PMBAB4nZFFbWZvkfQLkn5bktz9e5K+l28sAMBV5u6bH2D2TkknJX1R0lFJK5LudfeXrzluXtK8JFUqldmlpaU88iYzGAw0OTlZdIyxwTzTYp5plWGe9Xp9xd2rwx7LUtRVSU9Kus3dnzKzj0q66O5/vNHXVKtV7/V628mcu263q1qtVnSMscE802KeaZVhnma2YVFneTHxeUnPu/tT638+JemnUoUDAGxuZFG7+39J+rqZHV7/V8e0tg0CANgBWc/6WJD0yfUzPr4i6XfyiwQAeL1MRe3uT0sauncCAMgXVyYCQHAUNQAER1EDQHAUNQAER1EDQHAUNQAER1EDQHAUNQAER1EDQHAUNQAER1EDQHAUNQAER1EDQHAUNQAER1EDQHAUNQAER1EDQHAUNQAER1EDQHCZ7ploZs9J+q6kVUlX3J37JwLADsl6F3JJqrv7i7klAQAMxdYHAARn7j76ILOvSvqOJJf0N+5+csgx85LmJalSqcwuLS0ljprWYDDQ5ORk0THGBvNMi3lmU6/Xk67X6XSSrrcV9Xp9ZaNt5axF/YPu/oKZfb+kz0lacPfHNzq+Wq16r9e77sA7odvtqlarFR1jbDDPtJhnWlPHH9VzD95VdIxNmdmGRZ1p68PdX1j/5zclPSLpZ9LFAwBsZmRRm9kBMzt49XNJd0p6Nu9gAIA1Wc76qEh6xMyuHv/37n4m11QAgFeNLGp3/4qkozuQBQAwBKfnAUBwFDUABEdRA0BwFDUABEdRA0BwFDUABEdRA0BwFDUABEdRA0BwFDUABEdRA0BwFDUABEdRA0BwFDUABEdRA0BwWW4cACCDow88ppcuXd70mAsfvjvp9zx03+mRx9y4b6/O3X9n0u+7E7LMcyumjj+67TWKmiVFDSTy0qXLo2+g+uDom0lLaW9um6KgipBpnhmlmmdRs2TrAwCCo6gBILjMRW1me8zsC2Y2elMMu0a73dbMzIyOHTummZkZtdvtoiMBY2cre9T3SupLektOWVAy7XZbzWZTi4uLWl1d1Z49ezQ3NydJajQaBacDxkemZ9Rm9kOS7pL0t/nGQZm0Wi0tLi6qXq9rYmJC9Xpdi4uLarVaRUcDxkrWZ9R/LumPJB3c6AAzm5c0L0mVSkXdbne72XI1GAzCZ4yu3+9rdXVV3W731Xmurq6q3+/v2tmm+u9O/fNZxr+Pg0eO6yceOp5uwYe2v8TBI1K3e2D7C22Vu2/6IeluSX+1/nlN0ulRXzM7O+vRdTqdoiOU3vT0tC8vL7v7a/NcXl726enpAlMV59B9p5OtlfLnM2WunRRxnnnOUlLPN+jULFsft0l6n5k9J2lJ0u1m9olcfmugVJrNpubm5tTpdHTlyhV1Oh3Nzc2p2WwWHQ0YKyO3Ptz9Q5I+JElmVpP0QXf/QL6xUAZXXzBcWFhQv9/XkSNH1Gq1eCERSIwrE7EtjUZDjUYj6ZV0AN5oS0Xt7l1J3VySAACG4spEAAiOrQ8AYSV9E6Qzad49rwgUNYCQUr1znrRW+CnX22lsfQBAcBQ1AARHUQNAcBQ1AARHUQNAcBQ1AARHUQNAcBQ1AARHUQNAcBQ1AARHUQNAcBQ1AARHUQNAcBQ1AARHUQNAcBQ1AAQ3sqjN7AYz+3czO2dm583sgZ0IBgBYk+UOL/8r6XZ3H5jZXklPmNk/uvuTOWcDAChDUbu7Sxqs/3Hv+ofnGQoA8Bpb6+ERB5ntkbQi6Uck/aW73zfkmHlJ85JUqVRml5aWEkdNazAYaHJysugYY4N5SgsXFoqOsKETh04UHSEX9Xo96XqdTifpeltRr9dX3L069EF3z/wh6SZJHUkzmx03Ozvr0XU6naIjjBXm6X7ovtPJ1ko5z5S5yqoMP5+Ser5Bp27prA93/x9JXUnv2cYvDgDAFmQ56+MWM7tp/fN9ku6Q9KWccwEA1mU56+MHJD20vk/9JkmfdvfT+cYCAFyV5ayPZyS9aweyAACG4MpEAAguy9YHgIymjj+abrEzada6cd/eJOugOBQ1kMhzD96VbK2p448mXQ/lxtYHAARHUQNAcBQ1AARHUQNAcBQ1AARHUQNAcJyet4sdfeAxvXTp8sjjLnz47qTf99B9m78DwY379urc/Xcm/Z5AmVHUu9hLly5nO1f3wdHvWd7tdlWr1bYfSokvGgHGAFsfABAcRQ0AwVHUABAcRQ0AwVHUABAcRQ0AwVHUABAcRQ0AwVHUABDcyKI2s3eYWcfM+mZ23szu3YlgAIA1WS4hvyLpD939rJkdlLRiZp9z9y/mnA0AoAzPqN39P9397Prn35XUl/T2vIMBANaY++g33Hn1YLMpSY9LmnH3i9c8Ni9pXpIqlcrs0tJSwpjpDQYDTU5OFh2jUAsXFoqOsKETh04UHSEX9Xo96XqdTifpeuOqDP+/1+v1FXevDnssc1Gb2aSkz0tqufvDmx1brVa91+ttOehOSvlub2CeqTHPtMowTzPbsKgznfVhZnslfUbSJ0eVNAAgrSxnfZikRUl9d/9I/pEAAK+X5Rn1bZJ+Q9LtZvb0+scv55wLALBu5Ol57v6EJNuBLACAIbgyEQCCo6gBIDiKGgCCo6gBIDiKGgCCo6gBIDiKGgCCo6gBIDiKGgCCo6gBIDiKGgCCo6gBIDiKGgCCo6gBIDiKGgCCo6gBIDiKGgCCo6gBIDiKGgCCy3IX8o+Z2TfN7NmdCAQAeKMsz6g/Luk9OecAAGxgZFG7++OSvr0DWQAAQ0ykWsjM5iXNS1KlUlG320219BssXFhIt9hD6ZY6cehEusVKaDAY5PZ3vhsxz7TKPk9z99EHmU1JOu3uM1kWrVar3uv1thktX91uV7VaregYY4N5psU80yrDPM1sxd2rwx7jrA8ACI6iBoDgspye15b0b5IOm9nzZjaXfywAwFUjX0x098ZOBAEADMfWBwAER1EDQHAUNQAER1EDQHCZLnjZ8qJm35J0IfnCad0s6cWiQ4wR5pkW80yrDPM85O63DHsgl6IuAzPrbXQVELaOeabFPNMq+zzZ+gCA4ChqAAhuNxf1yaIDjBnmmRbzTKvU89y1e9QAUBa7+Rk1AJQCRQ0AwVHU2BYzm+LGxygDM/sTM/tg0TmuB0UNAMHtyqI2s8+a2YqZnV+/1yO2Z8LMHjKzZ8zslJntLzpQmZnZb67P8pyZ/V3RecrMzJpm9mUz+ydJh4vOc712ZVFL+l13n5VUlXSPmb2t6EAld1jSSXf/SUkXJf1ewXlKy8ymJTUl3e7uRyXdW3Ck0jKzWUnvl/QuSb8q6aeLTXT9dmtR32Nm5yQ9Kekdkn604Dxl93V3/9f1zz8h6eeKDFNyt0s65e4vSpK7f7vgPGX285IecfdX3P2ipH8oOtD1GnmHl3FjZjVJd0i61d1fMbOupBuKzDQGrj0Zn5Pzr5+J+aU0FrPcjc+ob5T0nfWS/jFJ7y460Bj4YTO7df3zhqQnigxTcv8s6devbseZ2VsLzlNmj0v6FTPbZ2YHJb236EDXazcW9Rmtvfj1jKQ/1dr2B7anL+m31mf6Vkl/XXCe0nL385Jakj6/vj33kYIjlZa7n5X0KUlPS/qMpH8pNNA2cAk5AAS3G59RA0CpUNQAEBxFDQDBUdQAEBxFDQDBUdQAEBxFDQDB/R/K0Bxh43aBDAAAAABJRU5ErkJggg==\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "df.boxplot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "9ec7b347",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>x</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>z</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   a  b  c  d\n",
       "x  1  2  3  4\n",
       "y  1  4  2  3\n",
       "z  1  2  3  4"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 比如上图，你认为大于等于8的数据是异常值，可以将其排除\n",
    "condition = df['c'] < 8\n",
    "new = df[condition]\n",
    "new"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ca8e931c",
   "metadata": {},
   "source": [
    "# 数据清洗（四）：数据类型转换\n",
    "astype()可以对某一列进行数据类型转换"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "8e770430",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>x</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y</th>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>z</th>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   a   b   c   d\n",
       "x  1   2   3   4\n",
       "y  5   6   7   8\n",
       "z  9  10  11  12"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame([[1, 2, 3, 4],\n",
    "                   [5, 6, 7, 8],\n",
    "                   [9, 10, 11, 12]], index=list('xyz'), columns=list('abcd'))\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "716cf919",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   a     b   c   d\n",
      "x  1   2.0   3   4\n",
      "y  5   6.0   7   8\n",
      "z  9  10.0  11  12\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "a      int64\n",
       "b    float64\n",
       "c     object\n",
       "d      int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 将b列转化为浮点型，将c列转化为字符串型，最后查看df的数据类型\n",
    "df['b'] = df['b'].astype(float)\n",
    "df['c'] = df['c'].astype(str)\n",
    "print(df)\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "47c81c20",
   "metadata": {},
   "source": [
    "# 数据清洗(五)：文本数据清洗\n",
    "假设x列为字符串数据类型，通过**df['x'].str.xxx()**即可调用字符串方法，对文本数据进行处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "d635291b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>指数代码</th>\n",
       "      <th>指数名称</th>\n",
       "      <th>发布日期</th>\n",
       "      <th>行情开始日期</th>\n",
       "      <th>指数简写</th>\n",
       "      <th>指数权重开始日期</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>000001.XSHG</td>\n",
       "      <td>上证指数</td>\n",
       "      <td>1991-07-15</td>\n",
       "      <td>2005-01-04</td>\n",
       "      <td>SZZS</td>\n",
       "      <td>2011-05-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>000002.XSHG</td>\n",
       "      <td>A股指数</td>\n",
       "      <td>1992-02-21</td>\n",
       "      <td>2005-01-04</td>\n",
       "      <td>AGZS</td>\n",
       "      <td>2011-05-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>000003.XSHG</td>\n",
       "      <td>B股指数</td>\n",
       "      <td>1992-02-21</td>\n",
       "      <td>2005-01-04</td>\n",
       "      <td>BGZS</td>\n",
       "      <td>2011-05-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>000004.XSHG</td>\n",
       "      <td>工业指数</td>\n",
       "      <td>1993-05-03</td>\n",
       "      <td>2005-01-04</td>\n",
       "      <td>GYZS</td>\n",
       "      <td>2011-05-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>399001.XSHE</td>\n",
       "      <td>深证成指</td>\n",
       "      <td>1995-01-23</td>\n",
       "      <td>2005-01-04</td>\n",
       "      <td>SZCZ</td>\n",
       "      <td>2009-09-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>399005.XSHE</td>\n",
       "      <td>中小板指</td>\n",
       "      <td>2006-01-24</td>\n",
       "      <td>2006-01-24</td>\n",
       "      <td>ZXBZ</td>\n",
       "      <td>2009-09-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>399006.XSHE</td>\n",
       "      <td>创业板指</td>\n",
       "      <td>2010-06-01</td>\n",
       "      <td>2010-06-01</td>\n",
       "      <td>CYBZ</td>\n",
       "      <td>2010-06-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>399007.XSHE</td>\n",
       "      <td>深证300</td>\n",
       "      <td>2009-11-04</td>\n",
       "      <td>2009-11-04</td>\n",
       "      <td>SZ300</td>\n",
       "      <td>2009-11-30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          指数代码   指数名称        发布日期      行情开始日期   指数简写    指数权重开始日期\n",
       "0  000001.XSHG   上证指数  1991-07-15  2005-01-04   SZZS  2011-05-31\n",
       "1  000002.XSHG   A股指数  1992-02-21  2005-01-04   AGZS  2011-05-31\n",
       "2  000003.XSHG   B股指数  1992-02-21  2005-01-04   BGZS  2011-05-31\n",
       "3  000004.XSHG   工业指数  1993-05-03  2005-01-04   GYZS  2011-05-31\n",
       "4  399001.XSHE   深证成指  1995-01-23  2005-01-04   SZCZ  2009-09-30\n",
       "5  399005.XSHE   中小板指  2006-01-24  2006-01-24   ZXBZ  2009-09-30\n",
       "6  399006.XSHE   创业板指  2010-06-01  2010-06-01   CYBZ  2010-06-30\n",
       "7  399007.XSHE  深证300  2009-11-04  2009-11-04  SZ300  2009-11-30"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\"指数.txt\", sep=' ')\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "ec076cf5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>指数代码</th>\n",
       "      <th>指数名称</th>\n",
       "      <th>发布日期</th>\n",
       "      <th>行情开始日期</th>\n",
       "      <th>指数简写</th>\n",
       "      <th>指数权重开始日期</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>000001.XSHG</td>\n",
       "      <td>上证指数</td>\n",
       "      <td>1991-07-15</td>\n",
       "      <td>2005-01-04</td>\n",
       "      <td>SZZS</td>\n",
       "      <td>2011-05-31</td>\n",
       "      <td>1991</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>000002.XSHG</td>\n",
       "      <td>A股指数</td>\n",
       "      <td>1992-02-21</td>\n",
       "      <td>2005-01-04</td>\n",
       "      <td>AGZS</td>\n",
       "      <td>2011-05-31</td>\n",
       "      <td>1992</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>000003.XSHG</td>\n",
       "      <td>B股指数</td>\n",
       "      <td>1992-02-21</td>\n",
       "      <td>2005-01-04</td>\n",
       "      <td>BGZS</td>\n",
       "      <td>2011-05-31</td>\n",
       "      <td>1992</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>000004.XSHG</td>\n",
       "      <td>工业指数</td>\n",
       "      <td>1993-05-03</td>\n",
       "      <td>2005-01-04</td>\n",
       "      <td>GYZS</td>\n",
       "      <td>2011-05-31</td>\n",
       "      <td>1993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>399001.XSHE</td>\n",
       "      <td>深证成指</td>\n",
       "      <td>1995-01-23</td>\n",
       "      <td>2005-01-04</td>\n",
       "      <td>SZCZ</td>\n",
       "      <td>2009-09-30</td>\n",
       "      <td>1995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>399005.XSHE</td>\n",
       "      <td>中小板指</td>\n",
       "      <td>2006-01-24</td>\n",
       "      <td>2006-01-24</td>\n",
       "      <td>ZXBZ</td>\n",
       "      <td>2009-09-30</td>\n",
       "      <td>2006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>399006.XSHE</td>\n",
       "      <td>创业板指</td>\n",
       "      <td>2010-06-01</td>\n",
       "      <td>2010-06-01</td>\n",
       "      <td>CYBZ</td>\n",
       "      <td>2010-06-30</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>399007.XSHE</td>\n",
       "      <td>深证300</td>\n",
       "      <td>2009-11-04</td>\n",
       "      <td>2009-11-04</td>\n",
       "      <td>SZ300</td>\n",
       "      <td>2009-11-30</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          指数代码   指数名称        发布日期      行情开始日期   指数简写    指数权重开始日期  year\n",
       "0  000001.XSHG   上证指数  1991-07-15  2005-01-04   SZZS  2011-05-31  1991\n",
       "1  000002.XSHG   A股指数  1992-02-21  2005-01-04   AGZS  2011-05-31  1992\n",
       "2  000003.XSHG   B股指数  1992-02-21  2005-01-04   BGZS  2011-05-31  1992\n",
       "3  000004.XSHG   工业指数  1993-05-03  2005-01-04   GYZS  2011-05-31  1993\n",
       "4  399001.XSHE   深证成指  1995-01-23  2005-01-04   SZCZ  2009-09-30  1995\n",
       "5  399005.XSHE   中小板指  2006-01-24  2006-01-24   ZXBZ  2009-09-30  2006\n",
       "6  399006.XSHE   创业板指  2010-06-01  2010-06-01   CYBZ  2010-06-30  2010\n",
       "7  399007.XSHE  深证300  2009-11-04  2009-11-04  SZ300  2009-11-30  2009"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 将发布日期这一列的年份提取出来，构成新的一列\n",
    "df['year'] = df['发布日期'].str.slice(0, 4)    # df['发布日期'].apply(lambda x: x[:4])\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "e44583bc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>指数代码</th>\n",
       "      <th>指数名称</th>\n",
       "      <th>发布日期</th>\n",
       "      <th>行情开始日期</th>\n",
       "      <th>指数简写</th>\n",
       "      <th>指数权重开始日期</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>399001.XSHE</td>\n",
       "      <td>深证成指</td>\n",
       "      <td>1995-01-23</td>\n",
       "      <td>2005-01-04</td>\n",
       "      <td>SZCZ</td>\n",
       "      <td>2009-09-30</td>\n",
       "      <td>1995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>399005.XSHE</td>\n",
       "      <td>中小板指</td>\n",
       "      <td>2006-01-24</td>\n",
       "      <td>2006-01-24</td>\n",
       "      <td>ZXBZ</td>\n",
       "      <td>2009-09-30</td>\n",
       "      <td>2006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>399006.XSHE</td>\n",
       "      <td>创业板指</td>\n",
       "      <td>2010-06-01</td>\n",
       "      <td>2010-06-01</td>\n",
       "      <td>CYBZ</td>\n",
       "      <td>2010-06-30</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>399007.XSHE</td>\n",
       "      <td>深证300</td>\n",
       "      <td>2009-11-04</td>\n",
       "      <td>2009-11-04</td>\n",
       "      <td>SZ300</td>\n",
       "      <td>2009-11-30</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          指数代码   指数名称        发布日期      行情开始日期   指数简写    指数权重开始日期  year\n",
       "4  399001.XSHE   深证成指  1995-01-23  2005-01-04   SZCZ  2009-09-30  1995\n",
       "5  399005.XSHE   中小板指  2006-01-24  2006-01-24   ZXBZ  2009-09-30  2006\n",
       "6  399006.XSHE   创业板指  2010-06-01  2010-06-01   CYBZ  2010-06-30  2010\n",
       "7  399007.XSHE  深证300  2009-11-04  2009-11-04  SZ300  2009-11-30  2009"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 将指数代码以XSHE结尾的行找出来\n",
    "condition = df['指数代码'].str.endswith('XSHE')\n",
    "df[condition]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "a504e2f4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>指数代码</th>\n",
       "      <th>指数名称</th>\n",
       "      <th>发布日期</th>\n",
       "      <th>行情开始日期</th>\n",
       "      <th>指数简写</th>\n",
       "      <th>指数权重开始日期</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>000001.XSHG</td>\n",
       "      <td>上证指数</td>\n",
       "      <td>1991-07-15</td>\n",
       "      <td>2005-01-04</td>\n",
       "      <td>szzs</td>\n",
       "      <td>2011-05-31</td>\n",
       "      <td>1991</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>000002.XSHG</td>\n",
       "      <td>A股指数</td>\n",
       "      <td>1992-02-21</td>\n",
       "      <td>2005-01-04</td>\n",
       "      <td>agzs</td>\n",
       "      <td>2011-05-31</td>\n",
       "      <td>1992</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>000003.XSHG</td>\n",
       "      <td>B股指数</td>\n",
       "      <td>1992-02-21</td>\n",
       "      <td>2005-01-04</td>\n",
       "      <td>bgzs</td>\n",
       "      <td>2011-05-31</td>\n",
       "      <td>1992</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>000004.XSHG</td>\n",
       "      <td>工业指数</td>\n",
       "      <td>1993-05-03</td>\n",
       "      <td>2005-01-04</td>\n",
       "      <td>gyzs</td>\n",
       "      <td>2011-05-31</td>\n",
       "      <td>1993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>399001.XSHE</td>\n",
       "      <td>深证成指</td>\n",
       "      <td>1995-01-23</td>\n",
       "      <td>2005-01-04</td>\n",
       "      <td>szcz</td>\n",
       "      <td>2009-09-30</td>\n",
       "      <td>1995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>399005.XSHE</td>\n",
       "      <td>中小板指</td>\n",
       "      <td>2006-01-24</td>\n",
       "      <td>2006-01-24</td>\n",
       "      <td>zxbz</td>\n",
       "      <td>2009-09-30</td>\n",
       "      <td>2006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>399006.XSHE</td>\n",
       "      <td>创业板指</td>\n",
       "      <td>2010-06-01</td>\n",
       "      <td>2010-06-01</td>\n",
       "      <td>cybz</td>\n",
       "      <td>2010-06-30</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>399007.XSHE</td>\n",
       "      <td>深证300</td>\n",
       "      <td>2009-11-04</td>\n",
       "      <td>2009-11-04</td>\n",
       "      <td>sz300</td>\n",
       "      <td>2009-11-30</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          指数代码   指数名称        发布日期      行情开始日期   指数简写    指数权重开始日期  year\n",
       "0  000001.XSHG   上证指数  1991-07-15  2005-01-04   szzs  2011-05-31  1991\n",
       "1  000002.XSHG   A股指数  1992-02-21  2005-01-04   agzs  2011-05-31  1992\n",
       "2  000003.XSHG   B股指数  1992-02-21  2005-01-04   bgzs  2011-05-31  1992\n",
       "3  000004.XSHG   工业指数  1993-05-03  2005-01-04   gyzs  2011-05-31  1993\n",
       "4  399001.XSHE   深证成指  1995-01-23  2005-01-04   szcz  2009-09-30  1995\n",
       "5  399005.XSHE   中小板指  2006-01-24  2006-01-24   zxbz  2009-09-30  2006\n",
       "6  399006.XSHE   创业板指  2010-06-01  2010-06-01   cybz  2010-06-30  2010\n",
       "7  399007.XSHE  深证300  2009-11-04  2009-11-04  sz300  2009-11-30  2009"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 将指数简写转换为小写的\n",
    "df['指数简写'] = df['指数简写'].str.lower()\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "8d9c526d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>指数代码</th>\n",
       "      <th>指数名称</th>\n",
       "      <th>发布日期</th>\n",
       "      <th>行情开始日期</th>\n",
       "      <th>指数简写</th>\n",
       "      <th>指数权重开始日期</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>000001.XSHG</td>\n",
       "      <td>上证指数</td>\n",
       "      <td>1991-07-15</td>\n",
       "      <td>20050104</td>\n",
       "      <td>szzs</td>\n",
       "      <td>2011-05-31</td>\n",
       "      <td>1991</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>000002.XSHG</td>\n",
       "      <td>A股指数</td>\n",
       "      <td>1992-02-21</td>\n",
       "      <td>20050104</td>\n",
       "      <td>agzs</td>\n",
       "      <td>2011-05-31</td>\n",
       "      <td>1992</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>000003.XSHG</td>\n",
       "      <td>B股指数</td>\n",
       "      <td>1992-02-21</td>\n",
       "      <td>20050104</td>\n",
       "      <td>bgzs</td>\n",
       "      <td>2011-05-31</td>\n",
       "      <td>1992</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>000004.XSHG</td>\n",
       "      <td>工业指数</td>\n",
       "      <td>1993-05-03</td>\n",
       "      <td>20050104</td>\n",
       "      <td>gyzs</td>\n",
       "      <td>2011-05-31</td>\n",
       "      <td>1993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>399001.XSHE</td>\n",
       "      <td>深证成指</td>\n",
       "      <td>1995-01-23</td>\n",
       "      <td>20050104</td>\n",
       "      <td>szcz</td>\n",
       "      <td>2009-09-30</td>\n",
       "      <td>1995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>399005.XSHE</td>\n",
       "      <td>中小板指</td>\n",
       "      <td>2006-01-24</td>\n",
       "      <td>20060124</td>\n",
       "      <td>zxbz</td>\n",
       "      <td>2009-09-30</td>\n",
       "      <td>2006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>399006.XSHE</td>\n",
       "      <td>创业板指</td>\n",
       "      <td>2010-06-01</td>\n",
       "      <td>20100601</td>\n",
       "      <td>cybz</td>\n",
       "      <td>2010-06-30</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>399007.XSHE</td>\n",
       "      <td>深证300</td>\n",
       "      <td>2009-11-04</td>\n",
       "      <td>20091104</td>\n",
       "      <td>sz300</td>\n",
       "      <td>2009-11-30</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          指数代码   指数名称        发布日期    行情开始日期   指数简写    指数权重开始日期  year\n",
       "0  000001.XSHG   上证指数  1991-07-15  20050104   szzs  2011-05-31  1991\n",
       "1  000002.XSHG   A股指数  1992-02-21  20050104   agzs  2011-05-31  1992\n",
       "2  000003.XSHG   B股指数  1992-02-21  20050104   bgzs  2011-05-31  1992\n",
       "3  000004.XSHG   工业指数  1993-05-03  20050104   gyzs  2011-05-31  1993\n",
       "4  399001.XSHE   深证成指  1995-01-23  20050104   szcz  2009-09-30  1995\n",
       "5  399005.XSHE   中小板指  2006-01-24  20060124   zxbz  2009-09-30  2006\n",
       "6  399006.XSHE   创业板指  2010-06-01  20100601   cybz  2010-06-30  2010\n",
       "7  399007.XSHE  深证300  2009-11-04  20091104  sz300  2009-11-30  2009"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 行情开始日期的短横线删除，只保留数字\n",
    "df['行情开始日期'] = df['行情开始日期'].str.replace('-', '')    # df['行情开始日期'].str.split('-').apply(lambda x: ''.join(x))\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d454aa34",
   "metadata": {},
   "source": [
    "# 数据索引方法：如何选取数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "67d3a786",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>x</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>z</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>w</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   a  b  c  d\n",
       "x  1  2  3  4\n",
       "y  1  4  2  3\n",
       "z  1  2  3  4\n",
       "w  1  2  8  5"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame([[1, 2, 3, 4],\n",
    "                   [1, 4, 2, 3],\n",
    "                   [1, 2, 3, 4],\n",
    "                   [1, 2, 8, 5]], index=list('xyzw'), columns=list('abcd'))\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b1ffaf41",
   "metadata": {},
   "source": [
    "### 选取行：loc或iloc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "cd3f9a63",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a    1\n",
       "b    2\n",
       "c    3\n",
       "d    4\n",
       "Name: x, dtype: int64"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 根据行标签选取一行\n",
    "df.loc['x']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "09df79ad",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>x</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   a  b  c  d\n",
       "x  1  2  3  4\n",
       "y  1  4  2  3"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 根据行标签选取多行\n",
    "df.loc[['x', 'y']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "70049930",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>x</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   a  b  c  d\n",
       "x  1  2  3  4\n",
       "y  1  4  2  3"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 根据位置选取一行或多行\n",
    "df.iloc[0:2]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "496597dc",
   "metadata": {},
   "source": [
    "### 选取列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "6eabf76b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "x    1\n",
       "y    1\n",
       "z    1\n",
       "w    1\n",
       "Name: a, dtype: int64"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 选取一列\n",
    "df['a']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "390bc0ca",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>c</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>x</th>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>z</th>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>w</th>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   a  c\n",
       "x  1  3\n",
       "y  1  2\n",
       "z  1  3\n",
       "w  1  8"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 选取多列\n",
    "df[['a', 'c']]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1a48e9fb",
   "metadata": {},
   "source": [
    "### 同时选取行和列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "98dc6985",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>b</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>x</th>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y</th>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>z</th>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   b  d\n",
       "x  2  4\n",
       "y  4  3\n",
       "z  2  4"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 先选取行，再选取列\n",
    "df.loc['x':'z'][['b','d']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "ee267d79",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>b</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>x</th>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y</th>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>z</th>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   b  d\n",
       "x  2  4\n",
       "y  4  3\n",
       "z  2  4"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 先选取列，再选取行\n",
    "df[['b','d']].iloc[0:3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "0e056a59",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>b</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>x</th>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y</th>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>z</th>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   b  d\n",
       "x  2  4\n",
       "y  4  3\n",
       "z  2  4"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 同时选取，用逗号隔开行和列的切片\n",
    "df.loc['x':'z', ['b','d']]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b2f907e1",
   "metadata": {},
   "source": [
    "### 根据条件选取数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "ab998233",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "x     True\n",
       "y     True\n",
       "z     True\n",
       "w    False\n",
       "Name: c, dtype: bool"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# c列小于4，得到一个布尔索引\n",
    "df['c'] < 4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "6318ef6c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>x</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>z</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   a  b  c  d\n",
       "x  1  2  3  4\n",
       "y  1  4  2  3\n",
       "z  1  2  3  4"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 可以根据这个布尔索引选取数据\n",
    "df[df['c'] < 4]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "7361f038",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>x</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>z</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   a  b  c  d\n",
       "x  1  2  3  4\n",
       "z  1  2  3  4"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 可以逻辑运算符号组合多个条件,如c列小于4，并且d列大于等于4\n",
    "# 注意逻辑运算符号不能用and, or, not; 而是 &, |, ~\n",
    "condition = (df['c'] < 4) & (df['d'] >= 4)\n",
    "df[condition]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "e391ade3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>y</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>w</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   a  b  c  d\n",
       "y  1  4  2  3\n",
       "w  1  2  8  5"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 上面的例子，根据condition的反条件选数据\n",
    "df[~condition]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b5ad9246",
   "metadata": {},
   "source": [
    "### 重设索引\n",
    "reset_index(): 将索引修改为0,...,n-1, 原索引变成新的一列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "a4b4f925",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>x</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>y</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>z</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>w</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  index  a  b  c  d\n",
       "0     x  1  2  3  4\n",
       "1     y  1  4  2  3\n",
       "2     z  1  2  3  4\n",
       "3     w  1  2  8  5"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7dc04040",
   "metadata": {},
   "source": [
    "### 修改索引名\n",
    "rename():修改索引名"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "d744aa43",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>aa</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>x</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>z</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>w</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   aa  b  c  d\n",
       "x   1  2  3  4\n",
       "y   1  4  2  3\n",
       "z   1  2  3  4\n",
       "w   1  2  8  5"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.rename(columns={'a': 'aa'})"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "98c6bf85",
   "metadata": {},
   "source": [
    "# 数据排序\n",
    "1. 对索引排序：sort_index()\n",
    "2. 对某列排序：sort_values()\n",
    "3. 生成排名值：rank()\n",
    "\n",
    "主要参数为ascending, ascending=True升序，ascending=False降序"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "c59f451d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>x</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>z</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>w</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   a  b  c  d\n",
       "x  1  2  3  4\n",
       "y  1  4  2  3\n",
       "z  1  2  3  0\n",
       "w  1  2  8  5"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame([[1, 2, 3, 4],\n",
    "                   [1, 4, 2, 3],\n",
    "                   [1, 2, 3, 0],\n",
    "                   [1, 2, 8, 5]], index=list('xyzw'), columns=list('abcd'))\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "a606a1f0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>y</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>x</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>z</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>w</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   a  b  c  d\n",
       "y  1  4  2  3\n",
       "x  1  2  3  4\n",
       "z  1  2  3  0\n",
       "w  1  2  8  5"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 对某一列排序\n",
    "df.sort_values('c')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "6456babe",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>y</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>z</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>x</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>w</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   a  b  c  d\n",
       "y  1  4  2  3\n",
       "z  1  2  3  0\n",
       "x  1  2  3  4\n",
       "w  1  2  8  5"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 对多列排序，例如，优先用c列排序，如果c列相同则根据d列决定顺序\n",
    "df.sort_values(['c', 'd'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "fc683f98",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>z</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>x</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>w</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   a  b  c  d\n",
       "z  1  2  3  0\n",
       "y  1  4  2  3\n",
       "x  1  2  3  4\n",
       "w  1  2  8  5"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 对索引排序\n",
    "df.sort_index(ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "3a424c62",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "      <th>b_rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>x</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>z</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>w</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>5</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   a  b  c  d  b_rank\n",
       "x  1  2  3  4     2.0\n",
       "y  1  4  2  3     4.0\n",
       "z  1  2  3  0     2.0\n",
       "w  1  2  8  5     2.0"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 生成b列的排名值\n",
    "df['b_rank'] = df['b'].rank()\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4e73d9e5",
   "metadata": {},
   "source": [
    "# 数据合并\n",
    "1. pd.concat(): 简单堆叠df\n",
    "2. pd.merge():根据某个键连接df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "93a56d1d",
   "metadata": {},
   "source": [
    "### pd.concat(df_list, axis): 简单堆叠"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "a27e192a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   姓名  年龄\n",
      "0  张三  20\n",
      "1  李四  21\n",
      "2  王五  24\n",
      "---------------------------\n",
      "   姓名  年龄\n",
      "0  赵六  19\n",
      "1  孙七  23\n"
     ]
    }
   ],
   "source": [
    "df1 = pd.DataFrame({\n",
    "    '姓名': ['张三', '李四', '王五'],\n",
    "    '年龄': [20, 21, 24]\n",
    "})\n",
    "df2 = pd.DataFrame({\n",
    "    '姓名': ['赵六', '孙七'],\n",
    "    '年龄': [19, 23]\n",
    "})\n",
    "print(df1)\n",
    "print('---------------------------')\n",
    "print(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "1e5bdd86",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>年龄</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>张三</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>李四</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>王五</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>赵六</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>孙七</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   姓名  年龄\n",
       "0  张三  20\n",
       "1  李四  21\n",
       "2  王五  24\n",
       "0  赵六  19\n",
       "1  孙七  23"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 将df1, df2沿垂直方向堆叠\n",
    "pd.concat([df1, df2], axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "127c7c91",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>年龄</th>\n",
       "      <th>姓名</th>\n",
       "      <th>年龄</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>张三</td>\n",
       "      <td>20</td>\n",
       "      <td>赵六</td>\n",
       "      <td>19.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>李四</td>\n",
       "      <td>21</td>\n",
       "      <td>孙七</td>\n",
       "      <td>23.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>王五</td>\n",
       "      <td>24</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   姓名  年龄   姓名    年龄\n",
       "0  张三  20   赵六  19.0\n",
       "1  李四  21   孙七  23.0\n",
       "2  王五  24  NaN   NaN"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 将df1, df2沿水平方向堆叠\n",
    "pd.concat([df1, df2], axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3211efa6",
   "metadata": {},
   "source": [
    "## pd.merge()：根据某一列，或者根据索引，连接、融合数据\n",
    "注意on, left_on, right_on, left_index, right_index, how这些参数的合理选用"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "959aec16",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   姓名  语文\n",
      "0  张三  77\n",
      "1  李四  84\n",
      "2  王五  92\n",
      "---------------------------\n",
      "   姓名  数学\n",
      "0  王五  93\n",
      "1  李四  87\n",
      "2  张三  62\n"
     ]
    }
   ],
   "source": [
    "df1 = pd.DataFrame({\n",
    "    '姓名': ['张三', '李四', '王五'],\n",
    "    '语文': [77, 84, 92]\n",
    "})\n",
    "df2 = pd.DataFrame({\n",
    "    '姓名': ['王五', '李四', '张三'],\n",
    "    '数学': [93, 87, 62]\n",
    "})\n",
    "print(df1)\n",
    "print('---------------------------')\n",
    "print(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "7a8d0f07",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>语文</th>\n",
       "      <th>数学</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>张三</td>\n",
       "      <td>77</td>\n",
       "      <td>62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>李四</td>\n",
       "      <td>84</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>王五</td>\n",
       "      <td>92</td>\n",
       "      <td>93</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   姓名  语文  数学\n",
       "0  张三  77  62\n",
       "1  李四  84  87\n",
       "2  王五  92  93"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 根据姓名列，将df1, df2连接成一个df\n",
    "pd.merge(df1, df2, on='姓名')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "98229e54",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  name  语文\n",
      "0   张三  77\n",
      "1   李四  84\n",
      "2   王五  92\n",
      "---------------------------\n",
      "   XM  数学\n",
      "0  王五  93\n",
      "1  李四  87\n",
      "2  张三  62\n"
     ]
    }
   ],
   "source": [
    "# 如果连接的列不一样，则需要分别指定左边的列和右边的列\n",
    "df3 = df1.rename(columns={'姓名': 'name'})\n",
    "df4 = df2.rename(columns={'姓名': 'XM'})\n",
    "print(df3)\n",
    "print('---------------------------')\n",
    "print(df4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "dea64ea7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>语文</th>\n",
       "      <th>XM</th>\n",
       "      <th>数学</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>张三</td>\n",
       "      <td>77</td>\n",
       "      <td>张三</td>\n",
       "      <td>62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>李四</td>\n",
       "      <td>84</td>\n",
       "      <td>李四</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>王五</td>\n",
       "      <td>92</td>\n",
       "      <td>王五</td>\n",
       "      <td>93</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  name  语文  XM  数学\n",
       "0   张三  77  张三  62\n",
       "1   李四  84  李四  87\n",
       "2   王五  92  王五  93"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df3, df4, left_on='name', right_on='XM')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "eb066fc8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      语文\n",
      "name    \n",
      "张三    77\n",
      "李四    84\n",
      "王五    92\n",
      "---------------------------\n",
      "    数学\n",
      "XM    \n",
      "王五  93\n",
      "李四  87\n",
      "张三  62\n"
     ]
    }
   ],
   "source": [
    "# 连接的键也可以是索引\n",
    "df33 = df3.set_index('name')  \n",
    "df44 = df4.set_index('XM')\n",
    "print(df33)\n",
    "print('---------------------------')\n",
    "print(df44)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "c3717e3d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>语文</th>\n",
       "      <th>数学</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>张三</th>\n",
       "      <td>77</td>\n",
       "      <td>62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>李四</th>\n",
       "      <td>84</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>王五</th>\n",
       "      <td>92</td>\n",
       "      <td>93</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    语文  数学\n",
       "张三  77  62\n",
       "李四  84  87\n",
       "王五  92  93"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df33, df44, left_index=True, right_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "2488cec9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  name  语文\n",
      "0   张三  77\n",
      "1   李四  84\n",
      "2   王五  92\n",
      "---------------------------\n",
      "    数学\n",
      "XM    \n",
      "王五  93\n",
      "李四  87\n",
      "张三  62\n"
     ]
    }
   ],
   "source": [
    "# 连接的键, 可以一个是索引，一个是列\n",
    "print(df3)\n",
    "print('---------------------------')\n",
    "print(df44)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "8b841fa6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>语文</th>\n",
       "      <th>数学</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>张三</td>\n",
       "      <td>77</td>\n",
       "      <td>62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>李四</td>\n",
       "      <td>84</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>王五</td>\n",
       "      <td>92</td>\n",
       "      <td>93</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  name  语文  数学\n",
       "0   张三  77  62\n",
       "1   李四  84  87\n",
       "2   王五  92  93"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df3, df44, left_on='name', right_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "2581eca7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   姓名  语文\n",
      "0  张三  77\n",
      "1  李四  84\n",
      "2  王五  92\n",
      "---------------------------\n",
      "   姓名  数学\n",
      "0  赵六  66\n",
      "1  王五  93\n",
      "2  李四  87\n",
      "3  张三  62\n"
     ]
    }
   ],
   "source": [
    "# how参数控制连接的方式, 默认是内连接inner\n",
    "df1 = pd.DataFrame({\n",
    "    '姓名': ['张三', '李四', '王五'],\n",
    "    '语文': [77, 84, 92]\n",
    "})\n",
    "df2 = pd.DataFrame({\n",
    "    '姓名': ['赵六', '王五', '李四', '张三'],\n",
    "    '数学': [66, 93, 87, 62]\n",
    "})\n",
    "print(df1)\n",
    "print('---------------------------')\n",
    "print(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "8944479b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>语文</th>\n",
       "      <th>数学</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>张三</td>\n",
       "      <td>77</td>\n",
       "      <td>62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>李四</td>\n",
       "      <td>84</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>王五</td>\n",
       "      <td>92</td>\n",
       "      <td>93</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   姓名  语文  数学\n",
       "0  张三  77  62\n",
       "1  李四  84  87\n",
       "2  王五  92  93"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 'inner'内连接\n",
    "pd.merge(df1, df2, on='姓名', how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "cd6f0fab",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>语文</th>\n",
       "      <th>数学</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>张三</td>\n",
       "      <td>77.0</td>\n",
       "      <td>62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>李四</td>\n",
       "      <td>84.0</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>王五</td>\n",
       "      <td>92.0</td>\n",
       "      <td>93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>赵六</td>\n",
       "      <td>NaN</td>\n",
       "      <td>66</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   姓名    语文  数学\n",
       "0  张三  77.0  62\n",
       "1  李四  84.0  87\n",
       "2  王五  92.0  93\n",
       "3  赵六   NaN  66"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 'outer'外连接\n",
    "pd.merge(df1, df2, on='姓名', how='outer')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "86a1fd1b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>语文</th>\n",
       "      <th>数学</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>张三</td>\n",
       "      <td>77</td>\n",
       "      <td>62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>李四</td>\n",
       "      <td>84</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>王五</td>\n",
       "      <td>92</td>\n",
       "      <td>93</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   姓名  语文  数学\n",
       "0  张三  77  62\n",
       "1  李四  84  87\n",
       "2  王五  92  93"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 'left'左连接\n",
    "pd.merge(df1, df2, on='姓名', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "3d2d4533",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>语文</th>\n",
       "      <th>数学</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>赵六</td>\n",
       "      <td>NaN</td>\n",
       "      <td>66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>王五</td>\n",
       "      <td>92.0</td>\n",
       "      <td>93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>李四</td>\n",
       "      <td>84.0</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>张三</td>\n",
       "      <td>77.0</td>\n",
       "      <td>62</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   姓名    语文  数学\n",
       "0  赵六   NaN  66\n",
       "1  王五  92.0  93\n",
       "2  李四  84.0  87\n",
       "3  张三  77.0  62"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 'right'右连接\n",
    "pd.merge(df1, df2, on='姓名', how='right')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8d73a957",
   "metadata": {},
   "source": [
    "# 统计函数\n",
    "value_counts(): 计算某列中各值出现的频率\n",
    "\n",
    "count(): 统计某列(某行)非空值的个数\n",
    "\n",
    "sum(): 求某列(某行)的和\n",
    "\n",
    "max(): 求某列(某行)的最大值\n",
    "\n",
    "min(): 求某列(某行)的最小值\n",
    "\n",
    "mean(): 求某列(某行)的平均值\n",
    "\n",
    "median(): 求某列(某行)的中位数\n",
    "\n",
    "var(): 求某列(某行)的方差\n",
    "\n",
    "std(): 求某列(某行)的标准差\n",
    "\n",
    "**corr()**: 计算列与列之间的**相关系数**\n",
    "\n",
    "apply(func): 应用自定义函数func\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "id": "7a52056a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>语文</th>\n",
       "      <th>数学</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>张三</td>\n",
       "      <td>77</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>李四</td>\n",
       "      <td>84</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>王五</td>\n",
       "      <td>92</td>\n",
       "      <td>63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>张三</td>\n",
       "      <td>78</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>王五</td>\n",
       "      <td>84</td>\n",
       "      <td>88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>李四</td>\n",
       "      <td>66</td>\n",
       "      <td>96</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   姓名  语文  数学\n",
       "0  张三  77  90\n",
       "1  李四  84  87\n",
       "2  王五  92  63\n",
       "3  张三  78  81\n",
       "4  王五  84  88\n",
       "5  李四  66  96"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame({\n",
    "    '姓名': ['张三', '李四', '王五', '张三', '王五', '李四'],\n",
    "    '语文': [77, 84, 92, 78, 84, 66],\n",
    "    '数学': [90, 87, 63, 81, 88, 96]\n",
    "})\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "b95ccdb7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "张三    2\n",
       "李四    2\n",
       "王五    2\n",
       "Name: 姓名, dtype: int64"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 统计某列中各值出现的频率\n",
    "df['姓名'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "5be37296",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>语文</th>\n",
       "      <th>数学</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>语文</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>-0.805117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>数学</th>\n",
       "      <td>-0.805117</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          语文        数学\n",
       "语文  1.000000 -0.805117\n",
       "数学 -0.805117  1.000000"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 计算相关系数\n",
    "df[['语文', '数学']].corr()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "e13345af",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 应用自定义的函数\n",
    "def fun(x):\n",
    "    # 如果得分是奇数，返回0, 否则返回100\n",
    "    if x % 2 == 1:\n",
    "        return 0\n",
    "    else:\n",
    "        return 100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "c9d7a52f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    100\n",
       "1      0\n",
       "2      0\n",
       "3      0\n",
       "4    100\n",
       "5    100\n",
       "Name: 数学, dtype: int64"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['数学'].apply(fun)   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "9608042b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    100\n",
       "1      0\n",
       "2      0\n",
       "3      0\n",
       "4    100\n",
       "5    100\n",
       "Name: 数学, dtype: int64"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['数学'].apply(lambda x: 0 if x % 2 == 1 else 100)    # lambda函数，三元表达式"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a24117a3",
   "metadata": {},
   "source": [
    "# 分组函数（groupby）\n",
    "1. 分组函数\n",
    "2. 分组函数的操作：应用统计函数，应用自定义函数，对每一列指定不同的函数，遍历分组对象\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "id": "fc181551",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>地方</th>\n",
       "      <th>语文</th>\n",
       "      <th>数学</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>张三</td>\n",
       "      <td>北京</td>\n",
       "      <td>77</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>李四</td>\n",
       "      <td>上海</td>\n",
       "      <td>84</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>张三</td>\n",
       "      <td>北京</td>\n",
       "      <td>92</td>\n",
       "      <td>63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>李四</td>\n",
       "      <td>上海</td>\n",
       "      <td>78</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>张三</td>\n",
       "      <td>深圳</td>\n",
       "      <td>84</td>\n",
       "      <td>88</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   姓名  地方  语文  数学\n",
       "0  张三  北京  77  90\n",
       "1  李四  上海  84  87\n",
       "2  张三  北京  92  63\n",
       "3  李四  上海  78  81\n",
       "4  张三  深圳  84  88"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame({\n",
    "    '姓名': ['张三', '李四', '张三', '李四', '张三'],\n",
    "    '地方': ['北京', '上海', '北京', '上海', '深圳'],\n",
    "    '语文': [77, 84, 92, 78, 84],\n",
    "    '数学': [90, 87, 63, 81, 88]\n",
    "})\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "id": "42b6323d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<pandas.core.groupby.generic.DataFrameGroupBy object at 0x000001881A6B5A30>\n",
      "<class 'pandas.core.groupby.generic.DataFrameGroupBy'>\n"
     ]
    }
   ],
   "source": [
    "# groupby函数返回groupby对象，是一个可迭代对象\n",
    "print(df.groupby('姓名'))\n",
    "print(type(df.groupby('姓名')))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "id": "c4768c33",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "张三\n",
      "   姓名  地方  语文  数学\n",
      "0  张三  北京  77  90\n",
      "2  张三  北京  92  63\n",
      "4  张三  深圳  84  88\n",
      "-----------------------------------------\n",
      "李四\n",
      "   姓名  地方  语文  数学\n",
      "1  李四  上海  84  87\n",
      "3  李四  上海  78  81\n",
      "-----------------------------------------\n"
     ]
    }
   ],
   "source": [
    "# 根据单列分组：根据姓名分组\n",
    "for name, group in df.groupby('姓名'):\n",
    "    print(name)\n",
    "    print(group)\n",
    "    print('-----------------------------------------')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "id": "a927d9f8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "张三 北京\n",
      "   姓名  地方  语文  数学\n",
      "0  张三  北京  77  90\n",
      "2  张三  北京  92  63\n",
      "-----------------------------------------\n",
      "张三 深圳\n",
      "   姓名  地方  语文  数学\n",
      "4  张三  深圳  84  88\n",
      "-----------------------------------------\n",
      "李四 上海\n",
      "   姓名  地方  语文  数学\n",
      "1  李四  上海  84  87\n",
      "3  李四  上海  78  81\n",
      "-----------------------------------------\n"
     ]
    }
   ],
   "source": [
    "# groupby()还可以根据多列分组: 根据姓名和地方进行分组\n",
    "for (name, place), group in df.groupby(['姓名', '地方']):\n",
    "    print(name, place)\n",
    "    print(group)\n",
    "    print('-----------------------------------------')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "id": "73509056",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "姓名\n",
       "张三    90\n",
       "李四    87\n",
       "Name: 数学, dtype: int64"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# grouby对象的操作： 应用统计函数\n",
    "df.groupby('姓名')['数学'].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "id": "ff36e121",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "姓名\n",
       "张三    84\n",
       "李四    78\n",
       "Name: 语文, dtype: int64"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# grouby对象的操作： 应用自定义函数: 获取语文第二高的分数\n",
    "df.groupby('姓名')['语文'].apply(lambda x: sorted(x)[-2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "id": "f3140b04",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>语文</th>\n",
       "      <th>数学</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>姓名</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>张三</th>\n",
       "      <td>84.333333</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>李四</th>\n",
       "      <td>81.000000</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           语文  数学\n",
       "姓名               \n",
       "张三  84.333333  90\n",
       "李四  81.000000  87"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# grouby对象的操作：agg()聚合函数, 同时对多列应用不同函数\n",
    "func_dic = {'语文': 'mean', '数学': 'max'}\n",
    "df.groupby('姓名').agg(func_dic)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "id": "05319bbc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>语文</th>\n",
       "      <th>数学</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>张三</td>\n",
       "      <td>84.333333</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>李四</td>\n",
       "      <td>81.000000</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   姓名         语文  数学\n",
       "0  张三  84.333333  90\n",
       "1  李四  81.000000  87"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# grouby对象的操作：遍历groupby对象，以上函数功能都可以通过遍历实现（万法归宗）\n",
    "lst = []\n",
    "for name, group in df.groupby('姓名'):\n",
    "    x = group['语文'].mean()\n",
    "    y = group['数学'].max()\n",
    "    lst.append([name, x, y])\n",
    "pd.DataFrame(lst, columns=['姓名', '语文', '数学'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6dfa6614",
   "metadata": {},
   "source": [
    "# 窗口函数\n",
    "窗口函数的分类：\n",
    "1. 滚动窗口: rolling()\n",
    "2. 扩展窗口: expanding()\n",
    "\n",
    "窗口函数的操作"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "id": "36bb27bc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2022-11-28</th>\n",
       "      <td>3055.29</td>\n",
       "      <td>3080.18</td>\n",
       "      <td>3034.70</td>\n",
       "      <td>3078.55</td>\n",
       "      <td>3.058113e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-11-29</th>\n",
       "      <td>3096.11</td>\n",
       "      <td>3152.00</td>\n",
       "      <td>3096.11</td>\n",
       "      <td>3149.75</td>\n",
       "      <td>3.914497e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-11-30</th>\n",
       "      <td>3141.40</td>\n",
       "      <td>3158.57</td>\n",
       "      <td>3137.37</td>\n",
       "      <td>3151.34</td>\n",
       "      <td>3.727183e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-12-01</th>\n",
       "      <td>3187.99</td>\n",
       "      <td>3198.41</td>\n",
       "      <td>3164.53</td>\n",
       "      <td>3165.47</td>\n",
       "      <td>3.870131e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-12-02</th>\n",
       "      <td>3160.58</td>\n",
       "      <td>3170.90</td>\n",
       "      <td>3149.84</td>\n",
       "      <td>3156.14</td>\n",
       "      <td>3.078125e+10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               open     high      low    close        volume\n",
       "2022-11-28  3055.29  3080.18  3034.70  3078.55  3.058113e+10\n",
       "2022-11-29  3096.11  3152.00  3096.11  3149.75  3.914497e+10\n",
       "2022-11-30  3141.40  3158.57  3137.37  3151.34  3.727183e+10\n",
       "2022-12-01  3187.99  3198.41  3164.53  3165.47  3.870131e+10\n",
       "2022-12-02  3160.58  3170.90  3149.84  3156.14  3.078125e+10"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('daily_price.csv', index_col=0)\n",
    "df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "id": "7c69687e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Rolling [window=5,center=False,axis=0,method=single]\n",
      "<class 'pandas.core.window.rolling.Rolling'>\n",
      "第1次循环\n",
      "               open     high      low    close        volume\n",
      "2022-02-10  3481.91  3488.86  3464.22  3485.91  3.556663e+10\n",
      "-----------循环分割线，rolling滚动窗口的大小:1--------------\n",
      "第2次循环\n",
      "               open     high      low    close        volume\n",
      "2022-02-10  3481.91  3488.86  3464.22  3485.91  3.556663e+10\n",
      "2022-02-11  3472.28  3500.15  3459.33  3462.95  3.613561e+10\n",
      "-----------循环分割线，rolling滚动窗口的大小:2--------------\n",
      "第3次循环\n",
      "               open     high      low    close        volume\n",
      "2022-02-10  3481.91  3488.86  3464.22  3485.91  3.556663e+10\n",
      "2022-02-11  3472.28  3500.15  3459.33  3462.95  3.613561e+10\n",
      "2022-02-14  3451.85  3457.26  3415.45  3428.88  3.152744e+10\n",
      "-----------循环分割线，rolling滚动窗口的大小:3--------------\n",
      "第4次循环\n",
      "               open     high      low    close        volume\n",
      "2022-02-10  3481.91  3488.86  3464.22  3485.91  3.556663e+10\n",
      "2022-02-11  3472.28  3500.15  3459.33  3462.95  3.613561e+10\n",
      "2022-02-14  3451.85  3457.26  3415.45  3428.88  3.152744e+10\n",
      "2022-02-15  3428.04  3447.49  3421.64  3446.09  2.755593e+10\n",
      "-----------循环分割线，rolling滚动窗口的大小:4--------------\n",
      "第5次循环\n",
      "               open     high      low    close        volume\n",
      "2022-02-10  3481.91  3488.86  3464.22  3485.91  3.556663e+10\n",
      "2022-02-11  3472.28  3500.15  3459.33  3462.95  3.613561e+10\n",
      "2022-02-14  3451.85  3457.26  3415.45  3428.88  3.152744e+10\n",
      "2022-02-15  3428.04  3447.49  3421.64  3446.09  2.755593e+10\n",
      "2022-02-16  3457.07  3475.06  3453.80  3465.83  2.749939e+10\n",
      "-----------循环分割线，rolling滚动窗口的大小:5--------------\n",
      "第6次循环\n",
      "               open     high      low    close        volume\n",
      "2022-02-11  3472.28  3500.15  3459.33  3462.95  3.613561e+10\n",
      "2022-02-14  3451.85  3457.26  3415.45  3428.88  3.152744e+10\n",
      "2022-02-15  3428.04  3447.49  3421.64  3446.09  2.755593e+10\n",
      "2022-02-16  3457.07  3475.06  3453.80  3465.83  2.749939e+10\n",
      "2022-02-17  3464.21  3480.97  3454.28  3468.04  2.972627e+10\n",
      "-----------循环分割线，rolling滚动窗口的大小:5--------------\n",
      "第7次循环\n",
      "               open     high      low    close        volume\n",
      "2022-02-14  3451.85  3457.26  3415.45  3428.88  3.152744e+10\n",
      "2022-02-15  3428.04  3447.49  3421.64  3446.09  2.755593e+10\n",
      "2022-02-16  3457.07  3475.06  3453.80  3465.83  2.749939e+10\n",
      "2022-02-17  3464.21  3480.97  3454.28  3468.04  2.972627e+10\n",
      "2022-02-18  3451.63  3490.76  3447.03  3490.76  2.951500e+10\n",
      "-----------循环分割线，rolling滚动窗口的大小:5--------------\n"
     ]
    }
   ],
   "source": [
    "# 滚动窗口对象：rolling(window=10)函数返回滚动窗口对象，是一个可迭代对象\n",
    "# 注意窗口的滚动方式：从头滚到尾，i为一个长度为5的df。(刚开始滚的时候长度不足5)\n",
    "print(df.rolling(window=5))\n",
    "print(type(df.rolling(window=5)))\n",
    "count = 0  # 只打印7次循环\n",
    "for i in df.rolling(window=5):\n",
    "    count += 1\n",
    "    if count > 7:     #只打印7次循环\n",
    "        break\n",
    "    print(f'第{count}次循环')\n",
    "    print(i)\n",
    "    print(f'-----------循环分割线，rolling滚动窗口的大小:{len(i)}--------------')\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "id": "c3f7e990",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>ma5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2022-02-10</th>\n",
       "      <td>3481.91</td>\n",
       "      <td>3488.86</td>\n",
       "      <td>3464.22</td>\n",
       "      <td>3485.91</td>\n",
       "      <td>3.556663e+10</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-02-11</th>\n",
       "      <td>3472.28</td>\n",
       "      <td>3500.15</td>\n",
       "      <td>3459.33</td>\n",
       "      <td>3462.95</td>\n",
       "      <td>3.613561e+10</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-02-14</th>\n",
       "      <td>3451.85</td>\n",
       "      <td>3457.26</td>\n",
       "      <td>3415.45</td>\n",
       "      <td>3428.88</td>\n",
       "      <td>3.152744e+10</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-02-15</th>\n",
       "      <td>3428.04</td>\n",
       "      <td>3447.49</td>\n",
       "      <td>3421.64</td>\n",
       "      <td>3446.09</td>\n",
       "      <td>2.755593e+10</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-02-16</th>\n",
       "      <td>3457.07</td>\n",
       "      <td>3475.06</td>\n",
       "      <td>3453.80</td>\n",
       "      <td>3465.83</td>\n",
       "      <td>2.749939e+10</td>\n",
       "      <td>3457.932</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-02-17</th>\n",
       "      <td>3464.21</td>\n",
       "      <td>3480.97</td>\n",
       "      <td>3454.28</td>\n",
       "      <td>3468.04</td>\n",
       "      <td>2.972627e+10</td>\n",
       "      <td>3454.358</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-02-18</th>\n",
       "      <td>3451.63</td>\n",
       "      <td>3490.76</td>\n",
       "      <td>3447.03</td>\n",
       "      <td>3490.76</td>\n",
       "      <td>2.951500e+10</td>\n",
       "      <td>3459.920</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               open     high      low    close        volume       ma5\n",
       "2022-02-10  3481.91  3488.86  3464.22  3485.91  3.556663e+10       NaN\n",
       "2022-02-11  3472.28  3500.15  3459.33  3462.95  3.613561e+10       NaN\n",
       "2022-02-14  3451.85  3457.26  3415.45  3428.88  3.152744e+10       NaN\n",
       "2022-02-15  3428.04  3447.49  3421.64  3446.09  2.755593e+10       NaN\n",
       "2022-02-16  3457.07  3475.06  3453.80  3465.83  2.749939e+10  3457.932\n",
       "2022-02-17  3464.21  3480.97  3454.28  3468.04  2.972627e+10  3454.358\n",
       "2022-02-18  3451.63  3490.76  3447.03  3490.76  2.951500e+10  3459.920"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 窗口函数的操作：可以直接应用统计函数或自定义函数\n",
    "# 5日移动平均价格：最近5天收盘价的平均值\n",
    "df['ma5'] = df['close'].rolling(window=5).mean()\n",
    "df.head(7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "id": "e0cf6071",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>ma5</th>\n",
       "      <th>middle</th>\n",
       "      <th>upper</th>\n",
       "      <th>lower</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2022-11-28</th>\n",
       "      <td>3055.29</td>\n",
       "      <td>3080.18</td>\n",
       "      <td>3034.70</td>\n",
       "      <td>3078.55</td>\n",
       "      <td>3.058113e+10</td>\n",
       "      <td>3091.080</td>\n",
       "      <td>3072.2825</td>\n",
       "      <td>3156.975427</td>\n",
       "      <td>2987.589573</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-11-29</th>\n",
       "      <td>3096.11</td>\n",
       "      <td>3152.00</td>\n",
       "      <td>3096.11</td>\n",
       "      <td>3149.75</td>\n",
       "      <td>3.914497e+10</td>\n",
       "      <td>3103.242</td>\n",
       "      <td>3081.3100</td>\n",
       "      <td>3157.835239</td>\n",
       "      <td>3004.784761</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-11-30</th>\n",
       "      <td>3141.40</td>\n",
       "      <td>3158.57</td>\n",
       "      <td>3137.37</td>\n",
       "      <td>3151.34</td>\n",
       "      <td>3.727183e+10</td>\n",
       "      <td>3114.128</td>\n",
       "      <td>3088.7085</td>\n",
       "      <td>3162.051723</td>\n",
       "      <td>3015.365277</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-12-01</th>\n",
       "      <td>3187.99</td>\n",
       "      <td>3198.41</td>\n",
       "      <td>3164.53</td>\n",
       "      <td>3165.47</td>\n",
       "      <td>3.870131e+10</td>\n",
       "      <td>3129.360</td>\n",
       "      <td>3097.0915</td>\n",
       "      <td>3164.799280</td>\n",
       "      <td>3029.383720</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-12-02</th>\n",
       "      <td>3160.58</td>\n",
       "      <td>3170.90</td>\n",
       "      <td>3149.84</td>\n",
       "      <td>3156.14</td>\n",
       "      <td>3.078125e+10</td>\n",
       "      <td>3140.250</td>\n",
       "      <td>3101.3585</td>\n",
       "      <td>3172.746200</td>\n",
       "      <td>3029.970800</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               open     high      low    close        volume       ma5  \\\n",
       "2022-11-28  3055.29  3080.18  3034.70  3078.55  3.058113e+10  3091.080   \n",
       "2022-11-29  3096.11  3152.00  3096.11  3149.75  3.914497e+10  3103.242   \n",
       "2022-11-30  3141.40  3158.57  3137.37  3151.34  3.727183e+10  3114.128   \n",
       "2022-12-01  3187.99  3198.41  3164.53  3165.47  3.870131e+10  3129.360   \n",
       "2022-12-02  3160.58  3170.90  3149.84  3156.14  3.078125e+10  3140.250   \n",
       "\n",
       "               middle        upper        lower  \n",
       "2022-11-28  3072.2825  3156.975427  2987.589573  \n",
       "2022-11-29  3081.3100  3157.835239  3004.784761  \n",
       "2022-11-30  3088.7085  3162.051723  3015.365277  \n",
       "2022-12-01  3097.0915  3164.799280  3029.383720  \n",
       "2022-12-02  3101.3585  3172.746200  3029.970800  "
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 布林通道：中轨为20日移动平均线，上轨为中轨+2个标准差，下轨为中轨-2个标准差\n",
    "df['middle'] = df['close'].rolling(window=20).mean()\n",
    "df['upper'] = df['middle'] + 2 * df['close'].rolling(window=20).std()\n",
    "df['lower'] = df['middle'] - 2 * df['close'].rolling(window=20).std()\n",
    "df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "id": "89f1f86a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Expanding [min_periods=1,center=False,axis=0,method=single]\n",
      "<class 'pandas.core.window.expanding.Expanding'>\n",
      "第1次循环\n",
      "               open     high      low    close        volume\n",
      "2022-02-10  3481.91  3488.86  3464.22  3485.91  3.556663e+10\n",
      "-----------循环分割线，expanding滚动窗口的大小:1----------------\n",
      "第2次循环\n",
      "               open     high      low    close        volume\n",
      "2022-02-10  3481.91  3488.86  3464.22  3485.91  3.556663e+10\n",
      "2022-02-11  3472.28  3500.15  3459.33  3462.95  3.613561e+10\n",
      "-----------循环分割线，expanding滚动窗口的大小:2----------------\n",
      "第3次循环\n",
      "               open     high      low    close        volume\n",
      "2022-02-10  3481.91  3488.86  3464.22  3485.91  3.556663e+10\n",
      "2022-02-11  3472.28  3500.15  3459.33  3462.95  3.613561e+10\n",
      "2022-02-14  3451.85  3457.26  3415.45  3428.88  3.152744e+10\n",
      "-----------循环分割线，expanding滚动窗口的大小:3----------------\n",
      "第4次循环\n",
      "               open     high      low    close        volume\n",
      "2022-02-10  3481.91  3488.86  3464.22  3485.91  3.556663e+10\n",
      "2022-02-11  3472.28  3500.15  3459.33  3462.95  3.613561e+10\n",
      "2022-02-14  3451.85  3457.26  3415.45  3428.88  3.152744e+10\n",
      "2022-02-15  3428.04  3447.49  3421.64  3446.09  2.755593e+10\n",
      "-----------循环分割线，expanding滚动窗口的大小:4----------------\n",
      "第5次循环\n",
      "               open     high      low    close        volume\n",
      "2022-02-10  3481.91  3488.86  3464.22  3485.91  3.556663e+10\n",
      "2022-02-11  3472.28  3500.15  3459.33  3462.95  3.613561e+10\n",
      "2022-02-14  3451.85  3457.26  3415.45  3428.88  3.152744e+10\n",
      "2022-02-15  3428.04  3447.49  3421.64  3446.09  2.755593e+10\n",
      "2022-02-16  3457.07  3475.06  3453.80  3465.83  2.749939e+10\n",
      "-----------循环分割线，expanding滚动窗口的大小:5----------------\n",
      "第6次循环\n",
      "               open     high      low    close        volume\n",
      "2022-02-10  3481.91  3488.86  3464.22  3485.91  3.556663e+10\n",
      "2022-02-11  3472.28  3500.15  3459.33  3462.95  3.613561e+10\n",
      "2022-02-14  3451.85  3457.26  3415.45  3428.88  3.152744e+10\n",
      "2022-02-15  3428.04  3447.49  3421.64  3446.09  2.755593e+10\n",
      "2022-02-16  3457.07  3475.06  3453.80  3465.83  2.749939e+10\n",
      "2022-02-17  3464.21  3480.97  3454.28  3468.04  2.972627e+10\n",
      "-----------循环分割线，expanding滚动窗口的大小:6----------------\n",
      "第7次循环\n",
      "               open     high      low    close        volume\n",
      "2022-02-10  3481.91  3488.86  3464.22  3485.91  3.556663e+10\n",
      "2022-02-11  3472.28  3500.15  3459.33  3462.95  3.613561e+10\n",
      "2022-02-14  3451.85  3457.26  3415.45  3428.88  3.152744e+10\n",
      "2022-02-15  3428.04  3447.49  3421.64  3446.09  2.755593e+10\n",
      "2022-02-16  3457.07  3475.06  3453.80  3465.83  2.749939e+10\n",
      "2022-02-17  3464.21  3480.97  3454.28  3468.04  2.972627e+10\n",
      "2022-02-18  3451.63  3490.76  3447.03  3490.76  2.951500e+10\n",
      "-----------循环分割线，expanding滚动窗口的大小:7----------------\n"
     ]
    }
   ],
   "source": [
    "# 扩展窗口对象：expanding()函数返回扩展窗口对象，是一个可迭代对象\n",
    "# 注意窗口的滚动方式：从头滚到尾，i为一个df。i的长度越滚越长\n",
    "df = pd.read_csv('daily_price.csv', index_col=0)\n",
    "df = df.head(20)\n",
    "print(df.expanding())\n",
    "print(type(df.expanding()))\n",
    "count = 0  # 只打印7次循环\n",
    "for i in df.expanding():\n",
    "    count += 1\n",
    "    if count > 7:     #只打印7次循环\n",
    "        break\n",
    "    print(f'第{count}次循环')\n",
    "    print(i)\n",
    "    print(f'-----------循环分割线，expanding滚动窗口的大小:{len(i)}----------------')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "id": "2a8b5bff",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>cum_vol</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2022-02-10</th>\n",
       "      <td>3481.91</td>\n",
       "      <td>3488.86</td>\n",
       "      <td>3464.22</td>\n",
       "      <td>3485.91</td>\n",
       "      <td>3.556663e+10</td>\n",
       "      <td>3.556663e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-02-11</th>\n",
       "      <td>3472.28</td>\n",
       "      <td>3500.15</td>\n",
       "      <td>3459.33</td>\n",
       "      <td>3462.95</td>\n",
       "      <td>3.613561e+10</td>\n",
       "      <td>7.170224e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-02-14</th>\n",
       "      <td>3451.85</td>\n",
       "      <td>3457.26</td>\n",
       "      <td>3415.45</td>\n",
       "      <td>3428.88</td>\n",
       "      <td>3.152744e+10</td>\n",
       "      <td>1.032297e+11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-02-15</th>\n",
       "      <td>3428.04</td>\n",
       "      <td>3447.49</td>\n",
       "      <td>3421.64</td>\n",
       "      <td>3446.09</td>\n",
       "      <td>2.755593e+10</td>\n",
       "      <td>1.307856e+11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-02-16</th>\n",
       "      <td>3457.07</td>\n",
       "      <td>3475.06</td>\n",
       "      <td>3453.80</td>\n",
       "      <td>3465.83</td>\n",
       "      <td>2.749939e+10</td>\n",
       "      <td>1.582850e+11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               open     high      low    close        volume       cum_vol\n",
       "2022-02-10  3481.91  3488.86  3464.22  3485.91  3.556663e+10  3.556663e+10\n",
       "2022-02-11  3472.28  3500.15  3459.33  3462.95  3.613561e+10  7.170224e+10\n",
       "2022-02-14  3451.85  3457.26  3415.45  3428.88  3.152744e+10  1.032297e+11\n",
       "2022-02-15  3428.04  3447.49  3421.64  3446.09  2.755593e+10  1.307856e+11\n",
       "2022-02-16  3457.07  3475.06  3453.80  3465.83  2.749939e+10  1.582850e+11"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 扩展窗口用的比较少，不再多举例子\n",
    "# 每日成交量的累加\n",
    "df['cum_vol'] = df['volume'].expanding().sum()\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aa619357",
   "metadata": {},
   "source": [
    "# 处理时间序列\n",
    "将索引(或列)转化为时间格式: pd.to_datetime()\n",
    "\n",
    "数据重采样：df.resample()\n",
    "\n",
    "重采样对象的操作：应用统计函数，应用自定义函数，每一列指定不同的函数\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "id": "271751ec",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2022-11-28</th>\n",
       "      <td>3055.29</td>\n",
       "      <td>3080.18</td>\n",
       "      <td>3034.70</td>\n",
       "      <td>3078.55</td>\n",
       "      <td>3.058113e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-11-29</th>\n",
       "      <td>3096.11</td>\n",
       "      <td>3152.00</td>\n",
       "      <td>3096.11</td>\n",
       "      <td>3149.75</td>\n",
       "      <td>3.914497e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-11-30</th>\n",
       "      <td>3141.40</td>\n",
       "      <td>3158.57</td>\n",
       "      <td>3137.37</td>\n",
       "      <td>3151.34</td>\n",
       "      <td>3.727183e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-12-01</th>\n",
       "      <td>3187.99</td>\n",
       "      <td>3198.41</td>\n",
       "      <td>3164.53</td>\n",
       "      <td>3165.47</td>\n",
       "      <td>3.870131e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-12-02</th>\n",
       "      <td>3160.58</td>\n",
       "      <td>3170.90</td>\n",
       "      <td>3149.84</td>\n",
       "      <td>3156.14</td>\n",
       "      <td>3.078125e+10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               open     high      low    close        volume\n",
       "2022-11-28  3055.29  3080.18  3034.70  3078.55  3.058113e+10\n",
       "2022-11-29  3096.11  3152.00  3096.11  3149.75  3.914497e+10\n",
       "2022-11-30  3141.40  3158.57  3137.37  3151.34  3.727183e+10\n",
       "2022-12-01  3187.99  3198.41  3164.53  3165.47  3.870131e+10\n",
       "2022-12-02  3160.58  3170.90  3149.84  3156.14  3.078125e+10"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('daily_price.csv', index_col=0)\n",
    "df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "id": "d2abf67b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dtype('O')"
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 查看索引的数据类型, 'O'代表object，其实就是str\n",
    "df.index.dtype"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "id": "ba13ed8d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dtype('<M8[ns]')"
      ]
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 将索引转化为日期格式\n",
    "df.index = pd.to_datetime(df.index)\n",
    "df.index.dtype"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "id": "5d0a1afc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2022-02-28    3462.31\n",
       "2022-03-31    3252.20\n",
       "2022-04-30    3047.06\n",
       "2022-05-31    3186.43\n",
       "2022-06-30    3398.62\n",
       "2022-07-31    3253.24\n",
       "2022-08-31    3202.14\n",
       "2022-09-30    3024.39\n",
       "2022-10-31    2893.48\n",
       "2022-11-30    3151.34\n",
       "2022-12-31    3156.14\n",
       "Freq: M, Name: close, dtype: float64"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 数据重采样：获取每月的close收盘价， 'M'代表月\n",
    "df.resample('M')['close'].last()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "id": "f29ee1c2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2022-02-28    4.340425e+11\n",
       "2022-03-31    8.702006e+11\n",
       "2022-04-30    7.477908e+11\n",
       "2022-05-31    6.747273e+11\n",
       "2022-06-30    8.739388e+11\n",
       "2022-07-31    6.880870e+11\n",
       "2022-08-31    7.050010e+11\n",
       "2022-09-30    5.490156e+11\n",
       "2022-10-31    4.138633e+11\n",
       "2022-11-30    6.869078e+11\n",
       "2022-12-31    6.948255e+10\n",
       "Freq: M, Name: volume, dtype: float64"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 数据重采样：获取每月的累计成交量，'M'代表月\n",
    "df.resample('M')['volume'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "id": "d1ca1e76",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2022-03-31    3500.29\n",
       "2022-06-30    3417.01\n",
       "2022-09-30    3424.84\n",
       "2022-12-31    3198.41\n",
       "Freq: Q-DEC, Name: high, dtype: float64"
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 数据重采样：获取每季的最高价格，'Q'代表季\n",
    "df.resample('Q')['high'].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "id": "f86946b8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "i: 2022-02-28 00:00:00\n",
      "               open     high      low    close        volume\n",
      "2022-02-10  3481.91  3488.86  3464.22  3485.91  3.556663e+10\n",
      "2022-02-11  3472.28  3500.15  3459.33  3462.95  3.613561e+10\n",
      "2022-02-14  3451.85  3457.26  3415.45  3428.88  3.152744e+10\n",
      "-------循环分割线, 第2月--------------\n",
      "i: 2022-03-31 00:00:00\n",
      "               open     high      low    close        volume\n",
      "2022-03-01  3471.36  3491.13  3465.72  3488.83  3.260017e+10\n",
      "2022-03-02  3478.29  3486.62  3467.00  3484.19  3.293677e+10\n",
      "2022-03-03  3495.93  3500.29  3473.34  3481.11  4.125943e+10\n",
      "-------循环分割线, 第3月--------------\n",
      "i: 2022-04-30 00:00:00\n",
      "               open     high      low    close        volume\n",
      "2022-04-01  3234.67  3287.23  3226.30  3282.72  3.782105e+10\n",
      "2022-04-06  3269.43  3288.11  3255.69  3283.43  4.296428e+10\n",
      "2022-04-07  3267.81  3290.26  3236.48  3236.70  4.026267e+10\n",
      "-------循环分割线, 第4月--------------\n",
      "i: 2022-05-31 00:00:00\n",
      "               open     high      low    close        volume\n",
      "2022-05-05  3044.85  3082.23  3042.12  3067.76  3.830729e+10\n",
      "2022-05-06  3011.32  3030.69  2992.72  3001.56  3.432642e+10\n",
      "2022-05-09  2990.20  3015.94  2983.61  3004.14  2.920616e+10\n",
      "-------循环分割线, 第5月--------------\n",
      "i: 2022-06-30 00:00:00\n",
      "               open     high      low    close        volume\n",
      "2022-06-01  3179.69  3190.61  3160.04  3182.16  3.656644e+10\n",
      "2022-06-02  3170.31  3197.28  3163.76  3195.46  3.617706e+10\n",
      "2022-06-06  3196.96  3237.07  3181.65  3236.37  4.220278e+10\n",
      "-------循环分割线, 第6月--------------\n",
      "i: 2022-07-31 00:00:00\n",
      "               open     high      low    close        volume\n",
      "2022-07-01  3400.26  3404.05  3378.36  3387.64  3.504893e+10\n",
      "2022-07-04  3381.82  3405.62  3364.09  3405.43  3.577379e+10\n",
      "2022-07-05  3411.13  3424.84  3372.06  3404.03  4.116997e+10\n",
      "-------循环分割线, 第7月--------------\n",
      "i: 2022-08-31 00:00:00\n",
      "               open     high      low    close        volume\n",
      "2022-08-01  3246.62  3264.30  3225.55  3259.96  2.922048e+10\n",
      "2022-08-02  3231.26  3231.26  3155.19  3186.27  3.941762e+10\n",
      "2022-08-03  3188.89  3217.55  3159.46  3163.67  3.248857e+10\n",
      "-------循环分割线, 第8月--------------\n",
      "i: 2022-09-30 00:00:00\n",
      "               open     high      low    close        volume\n",
      "2022-09-01  3196.54  3214.56  3181.63  3184.98  2.746605e+10\n",
      "2022-09-02  3189.64  3198.28  3173.79  3186.48  2.504569e+10\n",
      "2022-09-05  3183.95  3199.91  3172.04  3199.91  2.806756e+10\n",
      "-------循环分割线, 第9月--------------\n",
      "i: 2022-10-31 00:00:00\n",
      "               open     high      low    close        volume\n",
      "2022-10-10  3026.94  3029.45  2968.28  2974.15  2.434048e+10\n",
      "2022-10-11  2978.06  2986.91  2953.50  2979.79  2.086360e+10\n",
      "2022-10-12  2976.72  3025.51  2934.09  3025.51  2.480136e+10\n",
      "-------循环分割线, 第10月--------------\n",
      "i: 2022-11-30 00:00:00\n",
      "               open     high      low    close        volume\n",
      "2022-11-01  2899.50  2969.20  2896.76  2969.20  3.198096e+10\n",
      "2022-11-02  2960.65  3019.05  2954.95  3003.37  3.250782e+10\n",
      "2022-11-03  2981.20  3003.72  2977.72  2997.81  2.593486e+10\n",
      "-------循环分割线, 第11月--------------\n",
      "i: 2022-12-31 00:00:00\n",
      "               open     high      low    close        volume\n",
      "2022-12-01  3187.99  3198.41  3164.53  3165.47  3.870131e+10\n",
      "2022-12-02  3160.58  3170.90  3149.84  3156.14  3.078125e+10\n",
      "-------循环分割线, 第12月--------------\n"
     ]
    }
   ],
   "source": [
    "# resample其实也是生成了一个重采样可迭代对象，可以遍历它\n",
    "for i, group in df.resample('M'):\n",
    "    print('i:',i)\n",
    "    print(group.head(3))\n",
    "    print(f'-------循环分割线, 第{group.index.month.unique()[0]}月--------------')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "id": "31a3462e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2022-11-06</th>\n",
       "      <td>2893.20</td>\n",
       "      <td>3081.59</td>\n",
       "      <td>2885.09</td>\n",
       "      <td>3070.80</td>\n",
       "      <td>1.536037e+11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-11-13</th>\n",
       "      <td>3062.86</td>\n",
       "      <td>3117.74</td>\n",
       "      <td>3022.85</td>\n",
       "      <td>3087.29</td>\n",
       "      <td>1.492703e+11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-11-20</th>\n",
       "      <td>3100.87</td>\n",
       "      <td>3145.75</td>\n",
       "      <td>3074.50</td>\n",
       "      <td>3097.24</td>\n",
       "      <td>1.583671e+11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-11-27</th>\n",
       "      <td>3078.06</td>\n",
       "      <td>3118.12</td>\n",
       "      <td>3056.17</td>\n",
       "      <td>3101.69</td>\n",
       "      <td>1.488682e+11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-12-04</th>\n",
       "      <td>3055.29</td>\n",
       "      <td>3198.41</td>\n",
       "      <td>3034.70</td>\n",
       "      <td>3156.14</td>\n",
       "      <td>1.764805e+11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               open     high      low    close        volume\n",
       "2022-11-06  2893.20  3081.59  2885.09  3070.80  1.536037e+11\n",
       "2022-11-13  3062.86  3117.74  3022.85  3087.29  1.492703e+11\n",
       "2022-11-20  3100.87  3145.75  3074.50  3097.24  1.583671e+11\n",
       "2022-11-27  3078.06  3118.12  3056.17  3101.69  1.488682e+11\n",
       "2022-12-04  3055.29  3198.41  3034.70  3156.14  1.764805e+11"
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 数据重采样：agg()函数，对不同列使用不同的函数\n",
    "# df为每日交易行情，我们可以把它重采样成周线行情\n",
    "fun_dic = {'open': 'first', \n",
    "           'high': 'max', \n",
    "           'low': 'min', \n",
    "           'close': 'last', \n",
    "           'volume': 'sum'}\n",
    "week_df = df.resample('W').agg(fun_dic)\n",
    "week_df.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "92f7b5df",
   "metadata": {},
   "source": [
    "# 最后: DataFrame的遍历\n",
    "pandas主要是整体操作数据，虽然很少用到遍历每一行，但DataFrame还是可以遍历的\n",
    "1. itertuples(): 按行遍历，每行为named tuple(扩展版的元组), 速度快一点\n",
    "2. iterrows(): 按行遍历，每行为series,速度慢一点\n",
    "3. iteritems(): 按列遍历，每列为series,速度慢一点"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "id": "b6bf7654",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2022-11-30</th>\n",
       "      <td>3141.40</td>\n",
       "      <td>3158.57</td>\n",
       "      <td>3137.37</td>\n",
       "      <td>3151.34</td>\n",
       "      <td>3.727183e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-12-01</th>\n",
       "      <td>3187.99</td>\n",
       "      <td>3198.41</td>\n",
       "      <td>3164.53</td>\n",
       "      <td>3165.47</td>\n",
       "      <td>3.870131e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-12-02</th>\n",
       "      <td>3160.58</td>\n",
       "      <td>3170.90</td>\n",
       "      <td>3149.84</td>\n",
       "      <td>3156.14</td>\n",
       "      <td>3.078125e+10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               open     high      low    close        volume\n",
       "2022-11-30  3141.40  3158.57  3137.37  3151.34  3.727183e+10\n",
       "2022-12-01  3187.99  3198.41  3164.53  3165.47  3.870131e+10\n",
       "2022-12-02  3160.58  3170.90  3149.84  3156.14  3.078125e+10"
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('daily_price.csv', index_col=0)\n",
    "df = df.tail(3)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "id": "b8346c15",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Pandas(Index='2022-11-30', open=3141.4, high=3158.57, low=3137.37, close=3151.34, volume=37271832100.0)\n",
      "可以通过下标访问元素 2022-11-30 37271832100.0\n",
      "也可以通过.访问元素 2022-11-30 37271832100.0\n",
      "------------------------------------------------------\n",
      "Pandas(Index='2022-12-01', open=3187.99, high=3198.41, low=3164.53, close=3165.47, volume=38701305600.0)\n",
      "可以通过下标访问元素 2022-12-01 38701305600.0\n",
      "也可以通过.访问元素 2022-12-01 38701305600.0\n",
      "------------------------------------------------------\n",
      "Pandas(Index='2022-12-02', open=3160.58, high=3170.9, low=3149.84, close=3156.14, volume=30781248400.0)\n",
      "可以通过下标访问元素 2022-12-02 30781248400.0\n",
      "也可以通过.访问元素 2022-12-02 30781248400.0\n",
      "------------------------------------------------------\n"
     ]
    }
   ],
   "source": [
    "# itertuples（）：按行遍历，i为named tuple, 扩展版的元组，可以通过下标访问元素，也可以通过.访问元素\n",
    "for i in df.itertuples(index=True):\n",
    "    print(i)\n",
    "    print('可以通过下标访问元素', i[0], i[-1])\n",
    "    print('也可以通过.访问元素', i.Index, i.volume)\n",
    "    print('------------------------------------------------------')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "id": "5b0c65f2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2022-11-30\n",
      "open      3.141400e+03\n",
      "high      3.158570e+03\n",
      "low       3.137370e+03\n",
      "close     3.151340e+03\n",
      "volume    3.727183e+10\n",
      "Name: 2022-11-30, dtype: float64\n",
      "------------------------------------------------------\n",
      "2022-12-01\n",
      "open      3.187990e+03\n",
      "high      3.198410e+03\n",
      "low       3.164530e+03\n",
      "close     3.165470e+03\n",
      "volume    3.870131e+10\n",
      "Name: 2022-12-01, dtype: float64\n",
      "------------------------------------------------------\n",
      "2022-12-02\n",
      "open      3.160580e+03\n",
      "high      3.170900e+03\n",
      "low       3.149840e+03\n",
      "close     3.156140e+03\n",
      "volume    3.078125e+10\n",
      "Name: 2022-12-02, dtype: float64\n",
      "------------------------------------------------------\n"
     ]
    }
   ],
   "source": [
    "# iterrows（）: 按行遍历，row为Series\n",
    "for idx, row in df.iterrows():\n",
    "    print(idx)\n",
    "    print(row)\n",
    "    print('------------------------------------------------------')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "id": "fb53ae85",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "col; open\n",
      "2022-11-30    3141.40\n",
      "2022-12-01    3187.99\n",
      "2022-12-02    3160.58\n",
      "Name: open, dtype: float64\n",
      "------------------------------------------------------\n",
      "col; high\n",
      "2022-11-30    3158.57\n",
      "2022-12-01    3198.41\n",
      "2022-12-02    3170.90\n",
      "Name: high, dtype: float64\n",
      "------------------------------------------------------\n",
      "col; low\n",
      "2022-11-30    3137.37\n",
      "2022-12-01    3164.53\n",
      "2022-12-02    3149.84\n",
      "Name: low, dtype: float64\n",
      "------------------------------------------------------\n",
      "col; close\n",
      "2022-11-30    3151.34\n",
      "2022-12-01    3165.47\n",
      "2022-12-02    3156.14\n",
      "Name: close, dtype: float64\n",
      "------------------------------------------------------\n",
      "col; volume\n",
      "2022-11-30    3.727183e+10\n",
      "2022-12-01    3.870131e+10\n",
      "2022-12-02    3.078125e+10\n",
      "Name: volume, dtype: float64\n",
      "------------------------------------------------------\n"
     ]
    }
   ],
   "source": [
    "# iteritems(): 遍历列，每一列为series\n",
    "for col, data in df.iteritems():\n",
    "    print('col;', col)\n",
    "    print(data)\n",
    "    print('------------------------------------------------------')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "34d08d67",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
